Removing Text from Cell 1

[PUMPDESIGNER]

I have cells with mixed contents, numbers and text.
Is there a method to quickly extract just the number or delete the text?

The number is a dollar amount $200.00 format.
Dollar sign I do not care about, but I would like to get to that 200.00.

Thank you.

PUMPDESIGNER

 

[onlyadrafter]

Hello,

Here is some code, not mine, that works.

Sub Macro1()
For Each c In Range("a1:a10")
With c
For Digit = 1 To Len(.Value)
Num = Mid(.Value, Digit, 1)
If IsNumeric(Num) Then NumStr = NumStr & Num
Next
Range("C" & .Row).Value = Val(NumStr)
NumStr = ""
End With
Next c

End Sub

You will need to change range names as required.



----------------------------------
Hope this helps.
----------------------------------

maybe only a drafter
but the best user at this company!

 

[onlyadrafter]

Hello,

For the example given you could use

=VALUE(MID(A1,2,LEN(A1)))

where A1 = $200

but problems will be casued by other text strings.


----------------------------------
Hope this helps.
----------------------------------

maybe only a drafter
but the best user at this company!

 

[joerd]

If your text strings look something like: "The user pays $200.00 for the software", and you want to extract the 200.00 part, you can do the following:
1. search the string for the $ sign
2. then search the next space
3. take the substring between the positions found in steps 1. and 2.

So, if the string is in A1, you could enter in B1:
=FIND("$",A1)
And in C1:
=FIND(" ",A1,B1)
And in D1:
=(MID(A1,B1+1,C1-B1-1))
Finally, convert to a value in E1:
=VALUE(D1)

You can of course concatenate all this into one big formula:
=VALUE(MID(A1,FIND("$",A1)+1,FIND(" ",A1,FIND("$",A1))-FIND("$",A1)-1))

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[saxon]

pumpdesigner, Put the cursor on the cell you want to change,Goto the the format button, on the formatting list go to cells, click and you'll get a list of formatting options, including currency signs.

Hope this helps.
saxon

 

[jghrist]

If the text and numbers are lined up the same in all cells, and the cells are in one column, you can use Data|Text to Columns to separate the text from numbers.

 

[PUMPDESIGNER]

Thank you all for your advice.
I will try all the obviously correct suggestions and see which is easiest.

PUMPDESIGNER

 

[jhardy1]

Best of all - try “ASAP Utilities” - free download from

http://www.asap-utilities.com.

This is a great plug-in set of tools for adding a heap of added functionality to Excel - and it's free!

One of the many tools is called "Delete all text-characters in selection” – and it does exactly what is says.

For example, if a cell has the following string:

“Price $200.00 only”

If you apply this tool, you end up with numeric 200 as the result. By default, it formats the cell into currency format, so it displays as $200.00.

It’s a fantastic toolkit – I can’t recommend it strongly enough!

 

[PUMPDESIGNER]

Thank you JulianHardy, that was a very good tool indeed.
I just really like this internet, very many nice people.
I had a set of tools I downloaded from

http://www.J-Walk.com

and they were very good too but did not have many of the tools that asap has.

Thank you again.

PUMPDESIGNER

 

[trainguy]

You can just do a find/replace, searching for $ sign and replace with a blank.

It works!

tg