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

I'm getting old; there *must* be a simpler way.

Anyway:

=IF(OR(ISERROR(IF(ISERROR(FIND("+",A1)),0,FIND("+" ,A1))),FIND(".",A1)=IF(ISERROR(FIND("+",A1)),0,FI ND("+",A1))),A1,LEFT(A1,FIND(".",A1)-1)&RIGHT(A1,LEN(A1)-IF(ISERROR(FIND("+",A1)),0,FIND("+",A1))+1))

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Garbunkel" wrote in message ...
| 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!
| --
|

Using vba...

Function CleanItUp(ByRef strText As String)
Dim lngFirst As Long
Dim lngLast As Long
Const strMistake As String = "*.*+*"

If strText Like strMistake Then
lngFirst = InStr(1, strText, ".", vbTextCompare)
lngLast = InStr(1, strText, "+", vbTextCompare)
CleanItUp = Left$(strText, lngFirst - 1) & Right$(strText, Len(strText) - lngLast + 1)
End If
End Function


Sub GetStarted()
MsgBox CleanItUp("282_994.01+282_995.01")
End Sub
------------
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware





"Garbunkel"
wrote in message
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!
--

Select the range of cells that contain the problematic cells and run the
macro. Test on a copy of your data.

Sub bBBB()
Dim cell As Range, iloc1 As Long
Dim iloc2 As Long

For Each cell In Selection
If cell.Value Like "*.*+*" Then
iloc1 = InStr(1, cell.Value, ".", vbTextCompare)
iloc2 = InStr(1, cell.Value, "+", vbTextCompare)
cell.Value = Left(cell.Value, iloc1 - 1) & _
Right(cell.Value, Len(cell.Value) - iloc2 + 1)
End If
Next
End Sub

--
Regards,
Tom Ogilvy


"Garbunkel" wrote in message
...
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!
--

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

"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