remove spaces
Posted by Dwight on November 02, 2001 11:35 AM
I combined three long columns containing first name, middle initial, and last name with formula: =b1&" "&c1&" "&d1, then froze the resulting full names in column a and deleted columns b, c, and d. Turns out quite a few of the cells in column c (middle initial) were blank, so a lot of my names in column a have 3 spaces between them. Need a formula which will leave no more than one space between each part of the names.
Can someone help?
Posted by IML on November 02, 2001 11:45 AM
If they were truly blank, I think you would only have 2 spaces. None the less, you could use the trim function, such as
=TRIM(B1&" "&C1&" "&D1)
Good luck
Posted by todd on November 02, 2001 12:11 PM
you could also try =b1&" "&if(c1="","",c1&" ")&d1
Posted by IML on November 02, 2001 12:40 PM
Mine is obviously superior, just look at the results under the likely scenario of Cher, Maddona, or Prince is entered in D1. Much cleaner.
Posted by Todd on November 02, 2001 12:50 PM
yeah, but he didn't say singers were in the database..
to be honest, i didn't know quite how trim worked, so i just posted an alternate solution.. no offense intended...
Posted by IML on November 02, 2001 12:52 PM
No offense taken (or meant) NT
Posted by Don C on November 05, 2001 5:05 AM
A formula is not needed. Highlight the resulting column and use the Replace command; in the "Find what" box type three spaces, in the "Replace with" box type one space.
=b1&" "&c1&" "&d1, then froze the resulting full names in column a and deleted columns b, c, and d. Turns out quite a few of the cells in column c (middle initial) were blank, so a lot of my names in column a have 3 spaces between them. Need a formula which will leave no more than one space between each part of the names.
