Excel Question - Numbers with text?

I’m trying to figure out if there’s a way that you can treat numbers with text, say $5.0B, as a number instead of text? So that you can divide it, etc? Thanks!

format the cell as a custom #"b" edit: or rather #0.0"B" … or however you want it

That would work if I was trying to formatting # data to looks like text - but the problem is I’m pulling in a field through a query and it comes through as text. So I need to make the text become a number somehow… JasonU Wrote: ------------------------------------------------------- > format the cell as a custom > > #"b" \> \> \> edit: or rather #0.0"B" … or however you want it

the quickest way I can think of to do this is to select the column, then do a find/replace (ctrl H) and replace B with E09, M with E06 and so on … otherwise you’d have to insert a column, write a formula to look for ‘B’ as the rightmost character, if present, build up the value etc.

Yeah I think I might just have to use the “LEFT” function…find and replace would work if this was static data, but I’m making an automated database DoubleDip Wrote: ------------------------------------------------------- > the quickest way I can think of to do this is to > select the column, then do a find/replace (ctrl H) > and replace B with E09, M with E06 and so on … > otherwise you’d have to insert a column, write a > formula to look for ‘B’ as the rightmost > character, if present, build up the value etc.

Try this formula (assumes the text you want to convert to numbers is in cell A1): =VALUE(LEFT(A1,LEN(A1)-1))

Anonymous Wrote: ------------------------------------------------------- > Try this formula (assumes the text you want to > convert to numbers is in cell A1): > > =VALUE(LEFT(A1,LEN(A1)-1)) Beat me to it! =VALUE(LEFT(A1,LEN(A1)-1))

hold up … this will just give 5.0 if there is $5.0B in A1. And the LEN function fails if the number is actually already a number (i.e., $5.0) You have to do a bit more, such as testing for the presence of a ‘B’ at the end, then doing the conversion =IF(RIGHT(A1,1)=“B”,VALUE(LEFT(A1,LEN(A1)-1))*1000000000,A1)