| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
help with functions to nest
Im using a formula like this to clean a name in COL A:
=TRIM(SUBSTITUTE(A2,",","")) John , Smith Clean output in COL C is "John Smith" And I also use =RIGHT(C2,LEN(C2)-FIND(" ",C2))&" "&LEFT(C2,SEARCH(" ",C2)-1) to invert the name "John Smith" to "Smith John" What can I do to nest this two Formulas in only one step? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
help with functions to nest
Wherever you've got "C2" put "TRIM(SUBSTITUTE(A2,",",""))"
Regards Trevor wrote in message oups.com... Im using a formula like this to clean a name in COL A: =TRIM(SUBSTITUTE(A2,",","")) John , Smith Clean output in COL C is "John Smith" And I also use =RIGHT(C2,LEN(C2)-FIND(" ",C2))&" "&LEFT(C2,SEARCH(" ",C2)-1) to invert the name "John Smith" to "Smith John" What can I do to nest this two Formulas in only one step? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
help with functions to nest
=RIGHT(TRIM(SUBSTITUTE(A2,",","")),LEN(TRIM(SUBSTI TUTE(A2,",","")))-FIND(" ",TRIM(SUBSTITUTE(A2,",",""))))&"
"&LEFT(TRIM(SUBSTITUTE(A2,",","")),SEARCH(" ",TRIM(SUBSTITUTE(A2,",","")))-1) As you can see, this not an improvement. Maybe it can be tuned, but in general, having intermediate results in separate cells is good for verifying correctness and doesn't hurt performance. -- Kind regards, Niek Otten Microsoft MVP - Excel wrote in message oups.com... | Im using a formula like this to clean a name in COL A: | =TRIM(SUBSTITUTE(A2,",","")) | John , Smith | Clean output in COL C is "John Smith" | | And I also use | =RIGHT(C2,LEN(C2)-FIND(" ",C2))&" "&LEFT(C2,SEARCH(" ",C2)-1) | to invert the name "John Smith" to "Smith John" | | What can I do to nest this two Formulas in only one step? | |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
help with functions to nest
See this same reply in .Misc:
=MID(A1&" "&A1,FIND(",",A1)+2,LEN(A1)-1) Assumes the format is always the same: Name<spaces,<spaceName Biff wrote in message oups.com... Im using a formula like this to clean a name in COL A: =TRIM(SUBSTITUTE(A2,",","")) John , Smith Clean output in COL C is "John Smith" And I also use =RIGHT(C2,LEN(C2)-FIND(" ",C2))&" "&LEFT(C2,SEARCH(" ",C2)-1) to invert the name "John Smith" to "Smith John" What can I do to nest this two Formulas in only one step? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
help with functions to nest
Ooops!
=MID(A1&" "&A1,FIND(",",A1)+2,LEN(A1)-1) Try this instead: =TRIM(MID(A1&" "&A1,FIND(",",A1)+2,LEN(A1)-1)) Biff "Biff" wrote in message ... See this same reply in .Misc: =MID(A1&" "&A1,FIND(",",A1)+2,LEN(A1)-1) Assumes the format is always the same: Name<spaces,<spaceName Biff wrote in message oups.com... Im using a formula like this to clean a name in COL A: =TRIM(SUBSTITUTE(A2,",","")) John , Smith Clean output in COL C is "John Smith" And I also use =RIGHT(C2,LEN(C2)-FIND(" ",C2))&" "&LEFT(C2,SEARCH(" ",C2)-1) to invert the name "John Smith" to "Smith John" What can I do to nest this two Formulas in only one step? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How can I nest ISTEXT and IF functions? | Excel Worksheet Functions | |||
| nest functions in CELL function | Excel Worksheet Functions | |||
| How do I nest these 3 IF functions? | Excel Worksheet Functions | |||
| Why not nest more than 7 functions in Excel formula??? | Excel Worksheet Functions | |||
| How can I nest more than seven functions in MS Excel? | Excel Worksheet Functions | |||
Linear Mode
