Delete blank spaces in string. (Trim does not do it).

[jdttek]

Hi
I have scanned numeric data off of a paper statement into an Excel Spreadsheet (using TextBridge / OCR). Surprisingly, all characters are consistently recognized and placed in the correct column!

Problem is, on occassion, a blank space is inserted between characters in the string (e.g. 1234 imports as 12 34). I have tried using TRIM to eliminate this space but it does not work for a single space. I am looking for a way to test the string and delete any spaces that may exist (back to 1234). Any solutions would be greatly appreciated.

Thanks
JDTTEK

 

[vbMax]

You can use the following function:

Function NoSpace(inString)
For Counter = 1 To Len(inString)
If (Mid(inString, Counter, 1) <> &quot; &quot Then
outString = outString + Mid(inString, Counter, 1)
End If
Next Counter
NoSpace = outString
End Function

Then, reference this function in your VBA code as follows:
aString = NoSpace(aString)

-vbMax

 

[sfvb]

You can use the replace function:

strText = Replace(strText, " ", "")

 

[TonyJollans]

Hi jdttek,

To remove ALL spaces from a string (in A1), use:

=SUBSTITUTE(A1," ","")

.. or, in code, to fix cell A1 in place ..

[A1] = WorksheetFunction.Substitute([A1]," ","")

Enjoy,
Tony

 

[combo]

Or, for the whole 1st worksheet:

[tt]Worksheets(1).UsedRange.Replace What:=&quot; &quot;, Replacement:=&quot;&quot;, LookAt:=xlPart[/tt]

combo