How to return figure only without unit

[Stevekent]

Hi, I have data in cells with unit behind the figure. Example, cells A1 = 10.21 m, A2 = 5.2 m, A3 = 30.14 m2 and A4 = 11.0 m. Now what formula to use so that Cell B1 would return 10.21, B2= 5.2, B3=30.14 and B4=11.0. Please help. Thanks

 

[JoeMo]

if the cells are formatted to include the units, simply change the format to Number with whatever number of decimal places you want. If the units are entered manually then the cells contain strings not numbers. In that case try:

=Left(A1,search(" ",A1)-1)+0

 

[Stevekent]

if the cells are formatted to include the units, simply change the format to Number with whatever number of decimal places you want. If the units are entered manually then the cells contain strings not numbers. In that case try:

=Left(A1,search(" ",A1)-1)+0

The formula seems not working Mr. Joe. I can't figure out how to solve it.

 

[ahmedmirza]

Hi, I have data in cells with unit behind the figure. Example, cells A1 = 10.21 m, A2 = 5.2 m, A3 = 30.14 m2 and A4 = 11.0 m. Now what formula to use so that Cell B1 would return 10.21, B2= 5.2, B3=30.14 and B4=11.0. Please help. Thanks

I think below 'workaround' will help..

1. Copy all the contents of column A to B
2. Select column B and press ctrl+f and click the replace tab
3. Find m2, replace all by blank
4. Find m, replace all by blank
5. Find (Space), replace all by blank

HTH

 

[ahmedmirza]

Another workaround

Using text to column feature

1. Copy all the contents of column A to B
2. Select column B and click on data ribbon 'text to columns'
3. Select "Delimited"
4. Check Space or Check other and insert "m"
5. Delete the extracted data in next columns and you have your numbers in Column B

HTH

 

[Stevekent]

Is there a way of using formula instead of what have been suggested which incurred more steps.

 

[István Hirsch]

JoeMo's formula should work. What do you get using that formula?

 

[JoeMo]

The formula seems not working Mr. Joe. I can't figure out how to solve it.

Put this in cell A1 in a new worksheet: 10.21 m. Then copy the formula in post#2 directly from your browser and paste it to cell B1. What do you get in B1? I'm assuming that the "m" in your data cells is not the result of custom formatting the cells to append " m" to numbers that are entered in the cells, thus leaving real numbers (not strings) in those cells.