Removing leading decimals for text list
 

Good Morning,
I have a list of text in column D. There are about 15000 rows. The vast
majority of the text is just words, however some text is preceeded by a
decimal point and two numerals. I want to get rid of the decimal and
the two numerals but keep the text.
*I have something like:*

apples
pears
.11 oranges
beets
.51 grapes

RESULT:
apples
pears
oranges
beets
grapes

Thanks for the help.


--
Casey


------------------------------------------------------------------------
Casey's Profile: http://www.excelforum.com/member.php...fo&userid=4545
View this thread: http://www.excelforum.com/showthread...hreadid=471438

Hi Casey

With your data in column A, enter in B1
=IF(NOT(ISERROR(FIND(" ",A1))),MID(A1,FIND(" ",A1)+1,255),A1)
Copy down column B

Regards

Roger Govier


Casey wrote:
Good Morning,
I have a list of text in column D. There are about 15000 rows. The vast
majority of the text is just words, however some text is preceeded by a
decimal point and two numerals. I want to get rid of the decimal and
the two numerals but keep the text.
*I have something like:*

apples
pears
11 oranges
beets
51 grapes

RESULT:
apples
pears
oranges
beets
grapes

Thanks for the help.

Roger,
The formula just removed the first word or the .11. But I thought I
could see where you were going, so I changed the first Find() from
blank to "." (decimal point) and it did just what I needed. I don't
know if the website dropped the decimal point or If you just had a
typo. Anyway, many thanks, you just saved me hours of tedious work. I'm
pretty good with number manipulation but the text stuff I need to bone
up on. Thanks again Roger. Here is my corrected version of the
formula.

IF(NOT(ISERROR(FIND(".",A1))),MID(A1,FIND(" ",A1)+1,255),A1) :)


--
Casey


------------------------------------------------------------------------
Casey's Profile: http://www.excelforum.com/member.php...fo&userid=4545
View this thread: http://www.excelforum.com/showthread...hreadid=471438

Hi Casey

I'm glad you were wide awake. It was my typo that omitted the "." and left
it as a space. Obviously, without the decimal point in the first find, it
found the first space after the word and gave you a string of 255 spaces
after it.

I'm pleased you were able to work it out despite my error.

Regards

Roger Govier


Casey wrote:
Roger,
The formula just removed the first word or the .11. But I thought I
could see where you were going, so I changed the first Find() from
blank to "." (decimal point) and it did just what I needed. I don't
know if the website dropped the decimal point or If you just had a
typo. Anyway, many thanks, you just saved me hours of tedious work. I'm
pretty good with number manipulation but the text stuff I need to bone
up on. Thanks again Roger. Here is my corrected version of the
formula.

IF(NOT(ISERROR(FIND(".",A1))),MID(A1,FIND(" ",A1)+1,255),A1) :)