Stripping a character from a text string/number
Posted by Craig on June 27, 2001 10:57 AM
I downloaded a .txt file into Excel. I have some numbers that contain an "*" at the end of the number to signify it is a total(example, 100.00*). I would like to strip this "*" off the number so I can perform calculations, Help?
Craig
Posted by Craig on June 27, 2001 11:22 AM
Whoo Hooo - figured it out!
I created 2 formulas:
1) FIND("*",A1,1) = gives the number that of the text position (example 100.00* = 6).
2) MID(A1,1,(A2 - 1)) = Assuming the FIND Fomula resides in cell A2, this formula will basically count from the left to right 5 positions.
it works that is all I care about...
Posted by IML on June 27, 2001 11:40 AM
Using find and replace
Craig,
One other route would be to highlight the column and use find and replace. As the asterick is a wildcard, preceed it with a tilde (~) in your find box.
Good luck.
Posted by IML on June 27, 2001 11:49 AM
Re: Using find and replace (addendum)
With the above, you would leave the replace field blank and replace all.
Another way would be to highlight the column and go to data - text to columns
Hit Next, Check Other under Delimiters and place and asterick in the box. Finally hit finish.
Again, nothing wrong with your way, just a few more options for you.
Posted by Craig on June 27, 2001 12:15 PM
Re: Using find and replace (addendum)
IML,
I tried the Find Replace option, but for some reason it was deleting the whole contents of the cell, I was confused I had used this approach before and it worked fine, but not this time.
I will give your other option a try as well, nothing wrong with learning something new :)
Posted by IML on June 27, 2001 12:18 PM
Re: Using find and replace (addendum)
The * is a wildcard that means any charater. So if you don't put the ~ Excel thinks find "every character" and replace with nothing.
Good luck
Posted by Craig on June 27, 2001 12:20 PM
Re: Using find and replace (addendum)
Gotcha! Thanks!
