Separating Text Numbers and symbols

[Fenix187]

Hi everyone, hope you are well and can assist me with this query.

My current data is showing up as $1,562K.

Need the data to separate so I only get 1562 etc.

I've already seen some previous formulas here which didn't work. Welcome to new formulas or VBA coding.

Thank you.

 

[Aladin Akyurek]

Does...

=SUBSTITUTE(SUBSTITUTE(UPPER(A2),"K",""),"$","")+0

work?

 

[Fenix187]

does...

=substitute(substitute(upper(a2),"k",""),"$","")+0

work?

perfect thank you!

 

[Fenix187]

Hi Thanks for the assist earlier. the formula worked really well. Last question. When number dont appear on my excel cell, I am presented with " - " (dash: Keyboared key = after Zero, secondary function underscore _).

Thank you.

 

[Aladin Akyurek]

Hi Thanks for the assist earlier. the formula worked really well. Last question. When number dont appear on my excel cell, I am presented with " - " (dash: Keyboared key = after Zero, secondary function underscore _).

Thank you.

Does this help?

 

[Rick Rothstein]

Does...

=SUBSTITUTE(SUBSTITUTE(UPPER(A2),"K",""),"$","")+0

work?

Do you really have to substitute the $ sign away? When I try this on my system, it works fine...

=SUBSTITUTE(UPPER(A2),"K","")+0

Did you have to do that because the $ sign is not the currency symbol for you local setting?

 

[Aladin Akyurek]

hi thanks for the assist earlier. The formula worked really well. Last question. When number dont appear on my excel cell, i am presented with " - " (dash: Keyboared key = after zero, secondary function underscore _).

Thank you.

does this help?

=if(a2="","",iferror(substitute(substitute(upper(a2),"k",""),"$","")+0,""))

 

[hiker95]

Aladin Akyurek,

I had to change the following:

=if(a2="","",iferror(substitute(substitute(upper(a2),"k",""),"$","")+0,""))

To this:

=IF(A2="","",IFERROR(SUBSTITUTE(SUBSTITUTE(UPPER(A2),"K",""),"$","")+0,""))

 

[Aladin Akyurek]

Aladin Akyurek,

I had to change the following:

=if(a2="","",iferror(substitute(substitute(upper(a2),"k",""),"$","")+0,""))

To this:

=IF(A2="","",IFERROR(SUBSTITUTE(SUBSTITUTE(UPPER(A2),"K",""),"$","")+0,""))

That was a vagary of the board software...

In fact:

=if(a2="","",iferror(substitute(substitute(upper(a2),"K",""),"$","")+0,""))

is okay too. But thanks.