Link to home
Start Free TrialLog in
Avatar of mmmcdee
mmmcdeeFlag 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
Avatar of David Atkin
David Atkin
Flag of United Kingdom of Great Britain and Northern Ireland image

Hello,

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

ASKER

There are Words in the field.  See the attached TRIMMED column BN on the attached Excel.
ASKER CERTIFIED SOLUTION
Avatar of byundt
byundt
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of mmmcdee

ASKER

byundt - The Function works nicely which (thankfully) will eliminate the SUBSTITUTE nightmare I was creating.  Thank you for your prompt response -
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
Avatar of 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!