RegEx Tokenize question

[adam_jones]

I have a project with multiple rooms that each have their *name* followed up by Temperature or Humidity or Room Pressure.

I need to create a new column that only has the room name and drops the Temperature so that I can group these by the actual room name and not include the temperature, humidity, or room pressure.

For Example - 

Sterile Storage Temperature

Sterile Storage Humidity

OR - 5 Temperature

OR - 5 Humidty

OR - 7 Temperature

OR - 7 Room Pressure

Needs to drop the last word but create a new column for a "Parent Column"

I have RegEx but not sure how to express only those words are to be dropped and create new column with just room name. 

[mst3k]

so you want like "OR - 7 Room Pressure" to change into just OR - 7?

i would use the parse mode of the regex tool, and i think something like this would work:

(.+\s-\s\d+)\s.+

[adam_jones]

Yes that is correct. The new column would just be a list of the "Parent" room without breaking down Pressure, Temp, and Humidity.

I'm extremely unfamiliar with Regular Expression, it is foreign but I know the RegEx tool is the way I would want to do it. I tried what you gave me and it just gave me nulls. Do I need to input anything into the expression?

Also each room could have multiple words and/or numbers in it, but they all end in either Temp, Pressure, or Humidity. They also will have multiple rows per room "child" group.

For a better example:

Pharmacy 1013C Temperature
Pharmacy 1015B Temperature
Pharmacy 1017A Temperature
ENDO Temperature
ENDO Humidity
CYSTO ROOM Temperature
CYSTO ROOM Humidity
Nursery Room 4190 Temperature

Nursery Room 4190 Temperature

Nursery Room 4190 Temperature

Nursery Room 4190 Humidity
Nursery 4170 Temperature

Nursery 4170 Temperature

Nursery 4170 Temperature
Nursery Room 4170 Humidity
NURSERY ROOM 7180 Temperature
NURSERY ROOM 7180 Humidity
Sterile Core Supply Humidity
Pharmacy 1042C Temperature
Pharmacy 1042B Temperature
Pharmacy 1042A Temperature

What I need in a new column:

Pharmacy 1013C 
Pharmacy 1015B 
Pharmacy 1017A 
ENDO 
ENDO 
CYSTO ROOM 
CYSTO ROOM 
Nursery Room 4190

Nursery Room 4190

Nursery Room 4190 
Nursery Room 4190 
Nursery 4170 

Nursery 4170 

Nursery 4170 
Nursery Room 4170 
NURSERY ROOM 7180 
NURSERY ROOM 7180 
Sterile Core Supply
Pharmacy 1042C 
Pharmacy 1042B 
Pharmacy 1042A 

[Luke_C · Accepted solution]

Hi @adam_jones 

I would use regular formulas or other tools, here's two examples:

1. Formula tool to find the location of the last word and take everything to the left
2. Find and replace on these 3 key words, wouldn't account for if they occur regularly in the rest of the field though.

[adam_jones]

Wow that was it. Way easier than I was trying to do it. Thank you!