Splitting a cell in two

[Verner]

I would like to split the contents of a cell into 2 parts (currently, I have a column of entrys that denote a distance - i.e, 40m
45m
50m ....etc)
Is there a way to split these cells so that the number number is in one cell and the 'm' is in the other? (a reverse 'concatenate' function?)

 

[MortenA]

there at least one way:

In the adjacant collum write the formula:
=left(<cell>;len(<cell>)-1)

This will give you the numerical value in this collum (<cell> refers to the cell where your original text string is).

I assume that there is allways only one letter (m) but that the could be 1,2,3 or more digits.

Best regards

Morten

 

[joerd]

You can consider using the Data|Text to columns method.

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[Verner]

Thanks MortenA but if I wanted to export the two segments to the cell (i.e., the number and the unit) to two different cells, is there an easy way to do this?

 

[melone]

Check out the LEFT, and RIGHT functions....

 

[lilliput1]

When you do split it with the Left & Right function check if the numbers would be interpreted as numbers & allow you do to math functions & lookup functions. I did a similar work where code numbers for company type & company type description were in one cell. After spliting them up I worked on another spreadsheet with code nos. I created a vlookup fuction to return the company type description corresponding to the code number. It did not work because the code numbers were interpreted as text not as numbers. I formated them as numbers but it did not work. I multiplied them by one (suprisingly it allowed this arthmitic operation) and entered the product on another column of cells. I then formated these cells as numbers then range value copied them over the code numbers in the original cell. The vlookup finally worked after that.

 

[jghrist]

To ensure that the left part is always treated as a number, use:

=VALUE(LEFT(<cell>,LEN(<cell>)-1))

Otherwise, if you do sorts or lookups, it will be treated as text.

 

[MortenA]

I only assumed that all units where &quot;m&quot; - in order to simplify things i though that you could just copy past this letter into any cell that you wanted.

Using the value funtion is OK but should not matter unless then cells used for the formula are formatted as text.

Best reagards

Morten

 

[mgp]

If you are trying to separate the unit from the value just because you want to be able to use the values for calculation, then it may be easier to custom format the cells to show &quot;m&quot; as a unit. You won't need to cells then.

Use <Format Cells><Number><custom>
enter 0.00 &quot;m&quot; in the type box

That may be a bit simpler than trying to split cells.

To get rid of all the &quot;m&quot;'s, in the original column, just do a replace, replacing &quot;m&quot; with nothing.

regards
Mogens