Log In
Register
Home
New Member
November 13, 2021
Offline
Help!
How do I remove LEADING and TRAILING spaces in a cell, but NOT the spaces between or within the string?
I've tried everything I know of to figure this out, but can't find a solution. We have part numbers on our system that intentionally look like this:
BA48282 1936 D
BA 524 X 18 N
BA 5811 1934 92
Yes, sometimes the part number will have 1 space, 2 spaces, or many spaces within the part number itself. This is by design. When we run reports to Excel and do something like a VLOOKUP, the parts may not always match. The biggest reason is because sometimes the part number comes into Excel with spaces AFTER the part number, and sometimes not.
Most people use TRIM(), but that removes all the spaces within the cell which could change the actual part number.
I've tried to use this formula =MID(A2,FIND(MID(TRIM(A2),1,1),A2),LEN(A2)), but that only removes the leading spaces. It does KEEP the spaces within the part number, but I need to also remove the trailing spaces.
Thanks,
Rose
July 16, 2010
Offline
Hi Rosemary,
I understand that the spaces in the middle of the text string can vary, but do they always have the same number of leading and trailing spaces and if so, you can specify this number in your MID formula calculation to skip/ignore.
BTW, it's always best to include a small Excel file with a sample of your real data that covers every scenario so we can get the answers to these questions without having to ask you.
Mynda
October 10, 2021
Offline
Hi Rosemary,
have you tried:
=RIGHT(A1, LEN(A1)-1)
=LEFT(A1, LEN(A1)-1)
October 5, 2010
Offline
Hi Rosemary,
With Power Query you can use the Text.TrimStart and Text.TrimEnd functions - see attached file
Text.TrimStart - PowerQuery M | Microsoft Docs
Text.TrimEnd - PowerQuery M | Microsoft Docs
= Text.TrimStart(Text.TrimEnd([Part Number]))
Regards
Phil
