Get rid of One Space

Memar

Board Regular
Joined
Sep 2, 2011
Messages
76
I just want to get rid of a space between a first name and a last name, but I want to keep the space between the first name and the middle name (for the ones with middle name). The fourmula that I use get rid of all the spaces. That doesn't work with my case ( cause the other column that I want to use in vlookup function formula is set up with the space between the first name and middle name). Would please let me how to modify the formula to get the desired result?

This is the formula I am using now =Substitute(A1," ","")

<TABLE style="WIDTH: 140pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=186><COLGROUP><COL style="WIDTH: 140pt; mso-width-source: userset; mso-width-alt: 6802" width=186><TBODY><TR style="HEIGHT: 13.5pt; mso-height-source: userset" height=18><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 140pt; HEIGHT: 13.5pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 1pt solid" class=xl65 height=18 width=186>Mulligan, Ruth K</TD></TR></TBODY></TABLE>
.
Desired result - Mulligan,Ruth K
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
I just want to get rid of a space between a first name and a last name, but I want to keep the space between the first name and the middle name (for the ones with middle name). The fourmula that I use get rid of all the spaces. That doesn't work with my case ( cause the other column that I want to use in vlookup function formula is set up with the space between the first name and middle name). Would please let me how to modify the formula to get the desired result?

This is the formula I am using now =Substitute(A1," ","")

<TABLE style="WIDTH: 140pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=186 border=0><COLGROUP><COL style="WIDTH: 140pt; mso-width-source: userset; mso-width-alt: 6802" width=186><TBODY><TR style="HEIGHT: 13.5pt; mso-height-source: userset" height=18><TD class=xl65 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; WIDTH: 140pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" width=186 height=18>Mulligan, Ruth K</TD></TR></TBODY></TABLE>
.
Desired result - Mulligan,Ruth K
Try this...

=SUBSTITUTE(A1,", ",",")
 
Upvote 0
Or:
=Substitute(A1," ","",1)
 
Upvote 0
Then it would be wrong. :)
I confess I read the title and just altered the formula, without looking too much at what the data was.
 
Upvote 0
why not

=SUBSTITUTE(A1,", ",",")

works for both of your examples... capture the , and the space together in your substitute
 
Upvote 0

Forum statistics

Threads
1,224,505
Messages
6,179,153
Members
452,891
Latest member
JUSTOUTOFMYREACH

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top