Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Remove spaces between words
In Excel is there a SHFT command that can remove spaces between words.
I need to concatenate the left four letters of the family name with the left two letters of the first name to achieve an individual code of six letters. However, some family names have a space. eg: De Vries Van Housten. I need to "Shift" the letters to the left to remove the space so that Janet De Vries becomes DEVRJA, not (as I'm getting) DE VJA Help me, I have a lot of these to do! Heather |
#2
|
|||
|
|||
If Janet in not in the cell and it is just De Vries
then you can use B1: De Vries C1: Janet D1: =UPPER(LEFT(SUBSTITUTE(B1," ",""),3) & left(C1,2)) If they are all in one cell what if you had "Nancy Jo Cotton" --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Heather Tavitian" <Heather wrote in message ... In Excel is there a SHFT command that can remove spaces between words. I need to concatenate the left four letters of the family name with the left two letters of the first name to achieve an individual code of six letters. However, some family names have a space. eg: De Vries Van Housten. I need to "Shift" the letters to the left to remove the space so that Janet De Vries becomes DEVRJA, not (as I'm getting) DE VJA Help me, I have a lot of these to do! Heather |
#3
|
|||
|
|||
Assuming the last name is in A1 and the first name in B1:
=LEFT(SUBSTITUTE(A1," ",""),4)&LEFT(B1,2) If the names are as you show, in one cell, this won't work. On Fri, 30 Sep 2005 16:43:01 -0700, "Heather Tavitian" <Heather wrote: In Excel is there a SHFT command that can remove spaces between words. I need to concatenate the left four letters of the family name with the left two letters of the first name to achieve an individual code of six letters. However, some family names have a space. eg: De Vries Van Housten. I need to "Shift" the letters to the left to remove the space so that Janet De Vries becomes DEVRJA, not (as I'm getting) DE VJA Help me, I have a lot of these to do! Heather |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Remove Spaces beginning Time entry | Excel Discussion (Misc queries) | |||
how do I remove empty spaces trailing a text string? | Excel Worksheet Functions | |||
How do you remove excess spaces from an Excel field? | Excel Discussion (Misc queries) | |||
remove spaces in text in excel | Excel Discussion (Misc queries) | |||
how do I remove spaces from cells that were pasted | Excel Discussion (Misc queries) |