VBA search for spaces in a string - conversion from european to american decimal notation

[exceltime]

Hi Folks,
I have a badly structured CSV file that I have to scan as input to spreadsheet. Is there a way in VBA to scan for a string of spaces using a string function like Instr. Also in VBA I am looking for a function to convert numbers that are formatted using the european system with a comma as decimal separator to the american system using a dot. Help from experts is appreciated. Thanks in advance.

 

[Teeroy]

The replace multiple spaces with a single space you could use a quick (and dirty) VBA recursive function, e.g.

Sub test()
x = RemoveSpaces("f   g     h")
MsgBox x
End Sub

Function RemoveSpaces(sInput As String)
Dim Temp As String
Temp = Replace(sInput, "  ", " ")
If Len(sInput) <> Len(Temp) Then
    Temp = RemoveSpaces(Temp)
End If
RemoveSpaces = Temp
End Function

To change Euro to US decimal you could use a worksheet function:

=SUBSTITUTE(C1,",",".") to change from comma to decimal place then copy|paste special|values to turn the results into numbers.

 

[exceltime]

Your program works very well thanks a lot. As to the conversion is there no VBA function that may help. Thanks.

 

[Rick Rothstein]

The replace multiple spaces with a single space you could use a quick (and dirty) VBA recursive function, e.g.

Sub test()
x = RemoveSpaces("f   g    h")
MsgBox x
End Sub

Function RemoveSpaces(sInput As String)
Dim Temp As String
Temp = Replace(sInput, "  ", " ")
If Len(sInput) <> Len(Temp) Then
    Temp = RemoveSpaces(Temp)
End If
RemoveSpaces = Temp
End Function

There is no need to use a separate function to reduce multiple spaces to single spaces... just call out to the worksheet's TRIM function instead.

Sub test()
  Dim SingleSpaced As String
  SingleSpaced = [COLOR=#0000FF][B]WorksheetFunction.Trim[/B][/COLOR]("f   g      h")
  MsgBox SingleSpaced
End Sub

 

[Teeroy]

Thanks Rick. I was under the impression that Trim only handled leading and trailing spaces. I should have experimented with it .

At least I got a reason to play with recursion; my mind didn't quite hurt enough .

 

[Rick Rothstein]

Thanks Rick. I was under the impression that Trim only handled leading and trailing spaces. I should have experimented with it .

VB's Trim function only does leading and trailing spaces, but WorksheetFunction.Trim calls the TRIM function that Excel uses for worksheet formulas and that removes leading and traiing spaces AND collapses multiple adjacent spaces down to single spaces.

 

[exceltime]

Sorry Folks. I missed the point that I can use SUBSTITUTE with the prefix WorkSheet function. That solves he problem. Thanks.