mmmcdee
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
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
ASKER
There are Words in the field. See the attached TRIMMED column BN on the attached Excel.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
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!
Can you not do a 'find and replace' for spaces? Or do you have words etc in it as well?