How to seperate measurements 1.375 in (35 mm)

[HotRod]

I have an excel table that has thousands of cells that are

"1.375 in (35 mm)" How can I seperate each measurement into a new cell?

e.g. "1.375" & "35"

 

[Don]

I have an excel table that has thousands of cells that are

"1.375 in (35 mm)" How can I seperate each measurement into a new cell?

e.g. "1.375" & "35"

Try inserting a column to the right of your column. Highlight the
initial and do a Find and replace (without quotes)
Find: "in ("
Replace with: "in|("
( | = Shit + \ )
Then do a text to columns with | as the delimiter. That should take
care of it.

The threemain concepts are as follows:
1. Set up a destination column so you don't overwrite anything
2. Find the common thread between all the cells where you can separate
the cell contents. Replacing spaces with something else creates more
break ponts than you need.
3. Replace with a delimter (single character) that isn't used anywhere
in the range. I use | because it's rarely used. You can use any
thing you want, but you need to make sure it's not part of the data.

 

[Guest]

From the Data Menu, select Text to culumns and you can select Space as your
delimiter, watch out for the second step, this is where you decide whether
the result is a number or a text cell, you can also select your open
parenthesis as delimiter and then do a find and replace of your closing
parenthesis for nothing.

 

[Guest]

Assuming the values are in one column,

1. Get rid of the open and close parentheses by doing a search and replace.
2. Your values should now look like this: 1.375 in 35 mm
3. At this point, you can select the entire column, then Data->Text to
Columns. Follow the instructions that show up. But basically, you'll want to
choose the "Delimited" option and choose space as the delimiter.