Removing leading decimals for text list

[Casey]

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 Govier]

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.
>
>

[Casey]

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)

[Roger Govier]

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