Truncating Cells

[chanmanx2k]

Hello I need the letters cut off on the end of these cells. The letters vary from 1-2 characters and there is no space after the number. Some numbers are also decimals.

1ML - 1
450G - 450
120ML - 120
1.5ML - 1.5

 

[SpillerBD]

Use Text To Columns, delimited by "-"
You can keep the text as it will end up in a separate column from the values, or Skip 'em.

 

[Scott Huish]

=LOOKUP(9.9999999E+307,--LEFT(A1,LEN(A1)-{2,1}))

Excel 2010
	A	B
1	1ML	1
2	450G	450
3	120ML	120
4	1.5ML	1.5
Sheet1

Cell Formulas
Range	Formula
B1	=LOOKUP(9.9999999E+307,--LEFT(A1,LEN(A1)-{2,1}))
B2	=LOOKUP(9.9999999E+307,--LEFT(A2,LEN(A2)-{2,1}))
B3	=LOOKUP(9.9999999E+307,--LEFT(A3,LEN(A3)-{2,1}))
B4	=LOOKUP(9.9999999E+307,--LEFT(A4,LEN(A4)-{2,1}))

 

[Dim Me as xlNoob]

Scott I've seen you use 9.9999999E+307 quite a lot and I understand how it works in the formula, but I just want to know why that number exactly? Would something like 9E+307 not be as effective? Or even 9E+300 for something easier to write/remember?

 

[Scott Huish]

See here: https://www.mrexcel.com/forum/excel-questions/102091-9-9999999-a.html

 

[chanmanx2k]

[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD]1ML[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]450G[/TD]
[TD]450[/TD]
[/TR]
[TR]
[TD]120ML[/TD]
[TD]120[/TD]
[/TR]
[TR]
[TD]1.5ML[/TD]
[TD]1.5[/TD]
[/TR]
</tbody>[/TABLE]

Sorry I think I wasnt clear enough. Column 2 is what I want as the end result.

 

[chanmanx2k]

I made a separate chart right next to each other and it worked. When I used the same formula for my existing sheet, I am getting #N/A

 

[Scott Huish]

Did you change the cell references? Can you give an example of what data it didn't work with?