"Garbunkel" wrote:

Thanks everybody for your feedback! This helped immensely.
--



"Garbunkel" wrote:

All,

I am extracting a certain column of data from an Excel file
(a .
Unfortunately, some of the data was entered incorrectly
(i.e. there should not be any data present in-between
a '.' and a '+', nor should there be any occurence of a '.'
before a '+'
Example: 282_994.01+282_995.01
needs to be changed to 282_994+282_995.01

A hard-coded find & replace will not work because there
are so many different offending text strings. What I
simply want to do is find any occurence of a '.'
before a '+' and eliminate all text from the '.' up to
(but not including) the '+'.

Any help is much appreciated.

Thanks!
--

This maybe of help.
I think your best bet is to find first occurence of "." and "+" with the
Find Function and then find the number of character with Lens function.
If the first occurence of "+" is greater than first occurence of "." carry
out the following - copy the below (7 cells) into excel - E18 to K18 and it
will be self explanatory
282_994.01+282_995.01 =FIND(".",E18) =FIND("+",E18)
=LEN(E18) =LEFT(E18,F18-1) =MID(E18,G18,H18-G18+1) =I18&J18

1st col is your data
2nd col value is 8 i.e first occurence of "."
3rd col value is 11 ditto "+"
4th col is 21 i.e no character in your data
5th col extract from your data upto character 8
6th col extract from your data from character 11 to end i.e 21
7th col Joins 5th and 6th col together - bingo

Akin
Akin