Splitting a cell in two
Splitting a cell in two
(OP)
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?)
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?)





RE: Splitting a cell in two
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
RE: Splitting a cell in two
Cheers,
Joerd
Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.
RE: Splitting a cell in two
RE: Splitting a cell in two
RE: Splitting a cell in two
RE: Splitting a cell in two
=VALUE(LEFT(<cell>,LEN(<cell>)-1))
Otherwise, if you do sorts or lookups, it will be treated as text.
RE: Splitting a cell in two
Using the value funtion is OK but should not matter unless then cells used for the formula are formatted as text.
Best reagards
Morten
RE: Splitting a cell in two
Use <Format Cells><Number><custom>
enter 0.00 "m" in the type box
That may be a bit simpler than trying to split cells.
To get rid of all the "m"'s, in the original column, just do a replace, replacing "m" with nothing.
regards
Mogens