Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need some help with name replacement type formulas
I am having some difficulties getting this to work, but here is what I have;
for arguments sake, we will say the first name is John, last name Doe, middle initial X. I have a list of 200 names that look like this, that do me no good in this format. Doe, John Doe, John X Doe, X John Now I can get a formula that resolves the first one: =CONCATENATE(RIGHT(H2,LEN(H2)-FIND("*",SUBSTITUTE(H2," ","*",LEN(H2)-LEN(SUBSTITUTE(H2," ","")))))&" "&LEFT(H2,FIND(" ",H2)-1)) However as you can probably tell, this falls apart on the second and third names...I need a formula that will fix both of them...and I cant quite seem to get it right. I know its gotta be a simple factor of an IF statement, I just can't get the math right as I am not very good with these types of functions =( |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need some help with name replacement type formulas
Have you considered using Data TextToColumns using Comma as the
delimiter.......then you can do it again on the second column using space as the delimiter......then the concatenation to re-construct the names should be much easier...... Vaya con Dios, Chuck, CABGx3 "havocdragon" wrote: I am having some difficulties getting this to work, but here is what I have; for arguments sake, we will say the first name is John, last name Doe, middle initial X. I have a list of 200 names that look like this, that do me no good in this format. Doe, John Doe, John X Doe, X John Now I can get a formula that resolves the first one: =CONCATENATE(RIGHT(H2,LEN(H2)-FIND("*",SUBSTITUTE(H2," ","*",LEN(H2)-LEN(SUBSTITUTE(H2," ","")))))&" "&LEFT(H2,FIND(" ",H2)-1)) However as you can probably tell, this falls apart on the second and third names...I need a formula that will fix both of them...and I cant quite seem to get it right. I know its gotta be a simple factor of an IF statement, I just can't get the math right as I am not very good with these types of functions =( |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need some help with name replacement type formulas
Yes that worked for the most part, but was much easier to clean up afterwards
=) "CLR" wrote: Have you considered using Data TextToColumns using Comma as the delimiter.......then you can do it again on the second column using space as the delimiter......then the concatenation to re-construct the names should be much easier...... Vaya con Dios, Chuck, CABGx3 "havocdragon" wrote: I am having some difficulties getting this to work, but here is what I have; for arguments sake, we will say the first name is John, last name Doe, middle initial X. I have a list of 200 names that look like this, that do me no good in this format. Doe, John Doe, John X Doe, X John Now I can get a formula that resolves the first one: =CONCATENATE(RIGHT(H2,LEN(H2)-FIND("*",SUBSTITUTE(H2," ","*",LEN(H2)-LEN(SUBSTITUTE(H2," ","")))))&" "&LEFT(H2,FIND(" ",H2)-1)) However as you can probably tell, this falls apart on the second and third names...I need a formula that will fix both of them...and I cant quite seem to get it right. I know its gotta be a simple factor of an IF statement, I just can't get the math right as I am not very good with these types of functions =( |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to Evaluate Dynamic DDE Formulas | Excel Worksheet Functions | |||
Cant find & delete invalid formulas or links | Excel Discussion (Misc queries) | |||
Printing formulas | Excel Discussion (Misc queries) | |||
copy worksheet to a new workbook with formulas .... but new file not point to old one | Excel Discussion (Misc queries) | |||
Database type Formulas | Excel Worksheet Functions |