Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How do I change "Doe, John" to "John Doe" in Excel?
Imported text from another program shows "Doe, John". Does anyone know how
to break it into "John" and "Doe"? |
#2
|
|||
|
|||
How do I change "Doe, John" to "John Doe" in Excel?
=RIGHT(A1,LEN(A1)-FIND(",",A1)-1)&" "&LEFT(A1,FIND(",",A1)-1)
-- Kind regards, Niek Otten "Moon" wrote in message ... Imported text from another program shows "Doe, John". Does anyone know how to break it into "John" and "Doe"? |
#3
|
|||
|
|||
How do I change "Doe, John" to "John Doe" in Excel?
Moon,
There are a few ways. A couple are here. Not too robust, I fear, Data - Text to Columns. Make sure there are empty columns to the right to contain the parsed stuff. Select your column. Data - Text to Columns, Delimited, Delimiter: comma. This will split Doe, John into two columns. It won't preserve the original data (the last name will wind up in the in the original column), and will use an additional column for the first name. If there are additional commas, it will use more columns. The space after the comma will be included in the first name. It sometimes doesn't put the data in the same rows as was the original. You can easily rearrange the columns laterally if you need the first name column at the left by selecting a column, then edge-dragging holding the Shift key. Another way is with two helper columns: Last Name: =LEFT(A2,SEARCH(",",A2)-1) First Name: =MID(A2,SEARCH(",",A2)+2,LEN(A2)) The first name formula depends on the space after the comma, and will lop off the first character of the first name if it isn't there. If that's a possibility, use: =MID(A2,SEARCH(", ",A2)+2,LEN(A2)) Note the space after the comma in the search string. This will fail with #VALUE! in such a situtation, which is likely better than a messed-up first name. Earl Kiosterud www.smokeylake.com "Moon" wrote in message ... Imported text from another program shows "Doe, John". Does anyone know how to break it into "John" and "Doe"? |
#4
|
|||
|
|||
How do I change "Doe, John" to "John Doe" in Excel?
On Thu, 10 Nov 2005 06:44:09 -0800, "Moon"
wrote: Imported text from another program shows "Doe, John". Does anyone know how to break it into "John" and "Doe"? If the last name always ends with a ",", and the first name is always the last word in the text string, then, with the string in A1: FN: =MID(A1,FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1), LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1,255) LN: =LEFT(A1,FIND(",",A1)-1) The formulas will give errors if there is no comma, or space in the string. If you require more complicated pattern matching, then post back, as regular expressions could be used to advantage here. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to change the color of all series in an excel chart in one go. | Charts and Charting in Excel | |||
how to change range for dynamic chart in excel 2000 with button? | Charts and Charting in Excel | |||
Excel renames sheet (repaired) when trying to change data from a . | Excel Worksheet Functions | |||
how do I change an excel template into a "workbook" | Excel Discussion (Misc queries) | |||
How to change the color of all series in an excel chart in one go. | Charts and Charting in Excel |