Create new columns by extracting decimals numbers and text from the same column

[Chalsea]

Hi there,

I tried to create two new columns ('Event' & 'Rotation Number') by extracting the data from column 'Rotation'. Currently, 'Rotation' column has a mixed of data both decimals and text, e.g. 0.4 & WSE. My desire out put are:

If Rotation is in decimal format, then add that to column 'Rotation Number', if it's in text format, then 'Rotation Number' should show as 0.

If the Rotation is in text format, then add that to column 'Event', if it's showing as '-' or 'na' then categorize those as Other as well. 

I've attached the excel file here. I tried to use formulas here but it didn't seem to do all the trick. For example, in the extracting 'Event' information, I use the regex see screenshot below, it didn't write 'Prez Day' as it is and instead showed as 'Other'. 

Similarly, for extracting the decimal numbers, it showed all 0. 

Can someone help me take a look? 

Much appreciated.

Chalsea

 

[fmvizcaino · Accepted solution]

Hi @Chalsea ,

I'm using the same idea as you but with a different regex expression. I'm identifying the numbers instead of texts.

Best,

Fernando Vizcaino

[Chalsea]

Hi @fmvizcaino Thank you so much! It worked! I like how you reverse the logic in the regex. Have a great day!

Chalsea