×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Removing Text from Cell

Removing Text from Cell

Removing Text from Cell

(OP)
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

RE: Removing Text from Cell

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!

RE: Removing Text from Cell

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!

RE: Removing Text from Cell

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.

RE: Removing Text from Cell

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

RE: Removing Text from Cell

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.

RE: Removing Text from Cell

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

PUMPDESIGNER

RE: Removing Text from Cell

Best of all - try “ASAP Utilities” - free download from 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!

RE: Removing Text from Cell

(OP)
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 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

RE: Removing Text from Cell

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

It works!

tg

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login



News


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close