MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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!