Using lookup with mid or if with mid

[fidelityfranchise]

I am new to the forum and have a question regarding which type of formula to use...

I have a set of values that export as text ($12.0K, $11.0K, $8.2K, etc.) and need to get the numerical portion out into its own cell. I know the right, left, and mid formulas can do this, however I am having difficulty since the number of characters changes.

For example, I need to pull the 12.0 out but the same formula does not work to pull out the 8.2, it also pulls out the K (8.2K)...

Can someone help me with what my formula should look like and if i should try to use a lookup to do this or an if formula?

Thanks,
AJ

 

[Momentman]

Welcome to the Forum

Try

Excel 2012
	A	B
1	$12.0k	12.0
2	$8.2k	8.2
3	$11.0k	11.0
4	$23.56k	23.56
Sheet3

Cell Formulas
Range	Formula
B1	=MID(A1,2,LEN(A1)-2)

 

[fidelityfranchise]

Worksheet Formulas Cell Formula B1 =MID(A1,2,LEN(A1)-2) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

This is exactly what I needed... For my knowledge, what does the -2 do?

 

[Momentman]

This is exactly what I needed... For my knowledge, what does the -2 do?

The -2 is to rid it of "$" and "k" i.e 2 characters

 

[GerryZ]

This is exactly what I needed... For my knowledge, what does the -2 do?

Hallo!
the Momentmann's formula works great but I'd suggest to Add +0, so you can convert a number stored as text in a real number
=MID(A1,2,LEN(A1)-2)+0
So from now on you can make calculation with this number, before not
regards

 

[Momentman]

Hallo!
the Momentmann's formula works great but I'd suggest to Add +0, so you can convert a number stored as text in a real number
=MID(A1,2,LEN(A1)-2)+0
So from now on you can make calculation with this number, before not
regards

Nice addition, I like this construct
=1*MID(A1,2,LEN(A1)-2)

 

[fidelityfranchise]

Nice addition, I like this construct
=1*MID(A1,2,LEN(A1)-2)

This revision helped a lot - gave me the number format which is exactly what i needed.

Thanks for the help guys.