Removing White Space Between Numbers in Excel

mmmcdee
mmmcdee used Ask the Experts™
on
I need to Remove any space between a 1,2 or 3 digit number immediately followed by a 3 digit number from a trimmed field.  As you can see by my attached I have overdone the SUBSTITTUE function.  I am looking for a better solution for the SUBSTITUTE..

EXAMPLE:  
342 444 BECOMES 342444
1 333 BECOMES 1333
42 777 BECOMES 42777
342 444 BECOMES 342444
1 333 BECOMES 1333
42 777 BECOMES 42777

I am running my SUBSTITUTE() off of column BN (See Columns BP and BQ).  I would like to fill Cell BO with BN with Removed spaces between a 1,2 or 3 digit number immediately followed by a 3 digit number from the trimmed field (BN).


I have been using SUBSTITUTE within Cells
Harvesting.xlsm
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
David AtkinTechnical Director
Top Expert 2015

Commented:
Hello,

Can you not do a 'find and replace' for spaces?  Or do you have words etc in it as well?

Author

Commented:
There are Words in the field.  See the attached TRIMMED column BN on the attached Excel.
Mechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
Commented:
You can use regular expressions in a user-defined function to remove the spaces with a formula like:
=SpaceRemover(BE4)

The code for the user-defined function goes in a regular module sheet.
Function SpaceRemover(s As String) As String
Dim RgExp As Object
Set RgExp = CreateObject("VBScript.RegExp")
With RgExp
    .Pattern = "(\d{1,3})(\s+)(\d{3}[\b|\D])"
    .Global = True
    SpaceRemover = .Replace(s, "$1$3")
End With
Set RgExp = Nothing
End Function

Open in new window

Starting with Angular 5

Learn the essential features and functions of the popular JavaScript framework for building mobile, desktop and web applications.

Author

Commented:
byundt - The Function works nicely which (thankfully) will eliminate the SUBSTITUTE nightmare I was creating.  Thank you for your prompt response -
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
mmmcdee,
I don't know if you have ever used regular expressions before, but they are both arcane and powerful. If you have other editing needs on the text, it may be possible to expand the pattern in line 5 to cover them--or to perform another manipulation in the same user-defined function.

Please continue to post in this thread if you want help in implementation. Doing so increases the value of this thread in the Experts Exchange database.

Brad

Author

Commented:
Thanks Brad - I am sure as this spreadsheet grows I will have some more related questions to ask - I rarely use the regular expressions due to multi-users touching the spreadsheets - I hope this will convince them to start on the regular expression path. Thanks - Great job!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial