Avatar of mmmcdee
mmmcdee
Flag for United States of America asked on

Removing White Space Between Numbers in Excel

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
Microsoft Excel

Avatar of undefined
Last Comment
mmmcdee

8/22/2022 - Mon
David Atkin

Hello,

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

ASKER
There are Words in the field.  See the attached TRIMMED column BN on the attached Excel.
ASKER CERTIFIED SOLUTION
byundt

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
mmmcdee

ASKER
byundt - The Function works nicely which (thankfully) will eliminate the SUBSTITUTE nightmare I was creating.  Thank you for your prompt response -
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
byundt

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
mmmcdee

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