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
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
Hi Rosemary,
have you tried:
=RIGHT(A1, LEN(A1)-1)
=LEFT(A1, LEN(A1)-1)
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
