split column

I have a column of data in the following format
column A
John B. Smith

What I want to do is split this column into three separate column so that it 
will appear as below
Column B    Column C    Column D
John                B.                Smith.

Is there a formula that can help me do this.


TIA



0
tlee1 (15)
9/27/2004 2:46:32 PM
excel 39879 articles. 2 followers. Follow

3 Replies
848 Views

Similar Articles

[PageSpeed] 23

Tlee,

If all the names follow the same format (you don't say), you can use Data -
Text to columns, using a space as the separator.  Make sure that the columns
to the right are empty before you start.

-- 
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"tlee" <tlee@lang.com> wrote in message
news:erc7oDKpEHA.1460@TK2MSFTNGP12.phx.gbl...
> I have a column of data in the following format
> column A
> John B. Smith
>
> What I want to do is split this column into three separate column so that
it
> will appear as below
> Column B    Column C    Column D
> John                B.                Smith.
>
> Is there a formula that can help me do this.
>
>
> TIA
>
>
>


0
nothanks4548 (968)
9/27/2004 3:08:56 PM
Unfortunately they do not.  Some have the middle name and some don't.


"Earl Kiosterud" <nothanks@nospam.com> wrote in message 
news:OeIMrPKpEHA.2612@TK2MSFTNGP15.phx.gbl...
> Tlee,
>
> If all the names follow the same format (you don't say), you can use 
> Data -
> Text to columns, using a space as the separator.  Make sure that the 
> columns
> to the right are empty before you start.
>
> -- 
> Earl Kiosterud
> mvpearl omitthisword at verizon period net
> -------------------------------------------
>
> "tlee" <tlee@lang.com> wrote in message
> news:erc7oDKpEHA.1460@TK2MSFTNGP12.phx.gbl...
>> I have a column of data in the following format
>> column A
>> John B. Smith
>>
>> What I want to do is split this column into three separate column so that
> it
>> will appear as below
>> Column B    Column C    Column D
>> John                B.                Smith.
>>
>> Is there a formula that can help me do this.
>>
>>
>> TIA
>>
>>
>>
>
> 


0
tlee1 (15)
9/27/2004 3:27:43 PM
What you might try to do, is to create "helper" columns, which would contain
text formulas, with which you could separate the first, middle (if any), and
last names into individual columns.
You would then remove the text formulas, leaving just the data behind.

Say your names are in column A.

Enter this formula in B1:
=LEFT(A1,FIND(" ",A1)-1)

Enter this formula in C1:
=IF(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))>1,MID(A1,FIND("
",A1)+1,LEN(A1)-(LEN(B1)+LEN(D1)+2)),"")

And enter this formula in D1:
=RIGHT(A1,LEN(A1)-SEARCH("^^^",SUBSTITUTE(A1,"
","^^^",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))

In the above formula, watch out for "word wrap".
There is a <space> between the quotes (" ") in both Substitute portions of
the formula.

Now, select B1:D1, and drag down to copy as needed.

After you have all your names separated, select columns B, C, and D.
Right click in the selection, and choose "Copy".
Right click *again*, and choose "PasteSpecial".
Click on "Values", then click <OK>, then <Esc>, and you should now have
*only* your data, which you can then copy to anywhere you wish.
-- 

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



"tlee" <tlee@lang.com> wrote in message
news:OdqfraKpEHA.324@TK2MSFTNGP11.phx.gbl...
Unfortunately they do not.  Some have the middle name and some don't.


"Earl Kiosterud" <nothanks@nospam.com> wrote in message
news:OeIMrPKpEHA.2612@TK2MSFTNGP15.phx.gbl...
> Tlee,
>
> If all the names follow the same format (you don't say), you can use
> Data -
> Text to columns, using a space as the separator.  Make sure that the
> columns
> to the right are empty before you start.
>
> -- 
> Earl Kiosterud
> mvpearl omitthisword at verizon period net
> -------------------------------------------
>
> "tlee" <tlee@lang.com> wrote in message
> news:erc7oDKpEHA.1460@TK2MSFTNGP12.phx.gbl...
>> I have a column of data in the following format
>> column A
>> John B. Smith
>>
>> What I want to do is split this column into three separate column so that
> it
>> will appear as below
>> Column B    Column C    Column D
>> John                B.                Smith.
>>
>> Is there a formula that can help me do this.
>>
>>
>> TIA
>>
>>
>>
>
>


0
ragdyer1 (4060)
9/27/2004 10:19:09 PM
Reply:

Similar Artilces:

Subtotal multiple columns and ...
This is my current code: Sub subTotal() Dim FirstRow As Long Dim LastRow As Long Dim iRow As Long Dim botCell As Range Dim topCell As Range Dim wks As Worksheet Set wks = ActiveSheet With wks FirstRow = 2 .Rows(FirstRow).Insert .Cells(FirstRow, "A").Value = "dummyVal" LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row Set topCell = .Cells(LastRow, "A") Set botCell = .Cells(LastRow, "A") For iRow = LastRow To FirstRow + 1 Step -1 ...

Hide column based on data value
I use auto-filter to switch a spreadsheet between a terse view and verbose view. Column B is auto-filtered, and it's either blank or non-blank. Call it a "tag column." Some column B cells have constants; others, formulas. It works fine. Can the same effect be achieved somehow to hide columns rather than rows? I'd like one row to be a "tag row." Then, I'd like to switch between terse view and verbose view by hiding columns based on what's in the tag row for each column. Can you suggest an easy way to do this without VBA? The "custom views" f...

SUM rows in column if cell in different column is empty
Hi, I have a spreadsheet that has many rows of data. I'm trying to get a total for a column ("G") in a cell ("A2") but only on rows where the cell in another column ("J") for that row is empty.I tried SUMIF and couldn't get it to work. Can this be done with some kind of formula? Any help would be appreciated. Thanks, gg =SUMIF(I:I,"",G:G) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) <nospam@iamtheonewho.com> wrote in message news:1173743011.019953.66950@v33g2000cwv.googlegroup...

Line-Column Charts #2
I am trying to chart 1 column and 2 line data series in one chart with two different axis. I figured out how to define which axis the data series references but I have forgotten how to define which data is to be displayed in a column or line format. Thanks can you riht click the axis concerned and click format and font and chang the color of the axis figures to that of the color of the chart. is this what you want. -- remove $$$ from email addresss to send email Mark G <MarkG@discussions.microsoft.com> wrote in message news:F89C608D-FEAE-44DF-9A15-528CE0EE2247@microsoft.com....

Multiply entire column...
I haven't worked with Excel for a number of years... I have an Excel price list...all prices are reflecting my COST... If there not a way to tell the entire column to display as "content o cell x 2.5"? I know that I can manually enter it into each cell ( A1*2.5 ) ( A2*2. ) etc...but is there not a way to get it to do the entire column?? Thanks in advance -- Message posted from http://www.ExcelForum.com Ajx22, enter 2.5 in a cell somewhere and copy it. Then select all the cells in Column A (you can click the column heading) and Edit > Paste Special > Multiply > OK....

Split tasks a splitting headache
Inherited another Project IMS. I am noticing right off that the previous scheduler has been starting tasks without their FS preds completing. Yeah, it does show up as a spit bar in the gantt chart. But, does anyone know a way to filter out just task that started before predecessor completion? I'm dealing with several thousand lines. Thanks. -- trailerpup ------------------------------------------------------------------------ trailerpup's Profile: http://forums.techarena.in/members/116596.htm View this thread: http://forums.techarena.in/microsoft-project/1290853.htm ...

How to randomly split a whole dataset into two sub-dataset?
Hi, At your possible convenience, might anyone please kindly answer my question? Thank you very much. How to "RANDOMLY" split the whole data set (n=2000) into two sub dataset (n=1000; n=1000) in SPSS or Excel? Thank you very much. Please take care Caroline zencaroline <zencaroline@gmail.com> writes: > How to "RANDOMLY" split the whole data set (n=2000) into two sub > dataset (n=1000; n=1000) in SPSS or Excel? Create a new variable whose value is randomly distributed. Sort the data on this variable. Take the first 1000 cases ...

unique with 2 columns
I have 2 columns with duplicates in the first column and no duplicates in the second. I neeed to only see one value for each in the left and only one of the accounts from the right. ie: abc 1234 abc 12345 bcd 251 eft 600 eft 607 I would like to see either of the abc with the 1234 or 12345 I would like to see the bcd since it's individual with the 251 I would like to see either of the eft with the 600 or the 607 Use a totals query and one of the aggregate functions (First, Last, Min, or Max) on the...

Unwanted replicated split screen
I know I could re-install excell but before I do, is there anyone here who can help me disable a setting that I have no idea how it came about. I can only describe it as 2 identical execl grids on the same worksheet. Accompanying image will show you. Help. Thanks. +-------------------------------------------------------------------+ |Filename: excel_display_duplication.GIF | |Download: http://www.excelforum.com/attachment.php?postid=3855 | +-------------------------------------------------------------------+ -- BoyLeroy -----------------------------------...

Column Headings
Firstly, Happy New Year to you all. In my Outlook 2003 IN BOX I have lost the FROM column. Can anybody tell me how to get it back? Cheers Greg Hi, Greg; Right-click any of the remaining column headers, select "field chooser," find the "from" field, and drag it to the row of column headers. And a Happy New Year to you, IanRoy "Greg" wrote: > Firstly, Happy New Year to you all. > > In my Outlook 2003 IN BOX I have lost the FROM column. > > Can anybody tell me how to get it back? > > Cheers > Greg > > > ...

Cannot insert Column
When I try to insert a column, the option is grayed out - it won't let me. Is this an issue with security that I may have inadvertantly changed? I made no conscious effort to do this, and can't fix. The worksheet is my own. thank.s Do you have your worksheet protected? "Buss" wrote: > When I try to insert a column, the option is grayed out - it won't let me. > > Is this an issue with security that I may have inadvertantly changed? I > made no conscious effort to do this, and can't fix. The worksheet is my own. > > thank.s ...

Can't find my Front End Folder after database split
I just split my database base to allow for ease of use for multiple users. Ererything, it seemed, went smoothly until i try to find the fron-end folder. It is nowhere to be found. Should it be label ****_fe.mbd like the back end? Please help. Thaanks. It will be called that, unless you typed something else in the save as dialog. I thought it ended up in the same folder as the backend after splitting. Easiest way is to go back to the original and split it again, this time take note of where you save it before you press the button. Jeanette Cunningham "Joe" <Joe@discussi...

Display Data On Split Form
Not sure how to ask this but here goes. In AC 2007 I have a split form that I want to display a value that will show regardless of the row I select in the datasheet. This is the number of vacation days one will have through-out the year and of course it changes as vacations days get used up. Any help will be appreciated, James "JamesJ" <jjy@darwin_roadrunner.com> wrote in message news:6A4DFFD9-3302-4A10-B51A-153373D266FD@microsoft.com... > Not sure how to ask this but here goes. > In AC 2007 I have a split form that I want to display a value > that will show regardl...

sum column with 2 if criteria
=SUMPRODUCT(--('Working Copy'!A3:A1038="WCenterA"),--('Working Copy'!J3:J1038="1"),'Working Copy'!K3:K1038) this is the formula I entered as an array and I get an N/A#. I'm trying to look match 2 criteria and then add the remaining cells in column K. Instead of "WCenterA" I'd like to use a cell reference. I figured a combination of If and SumIF formulas, but couldn't get that to work either! Help!!! Barbara SUMPRODUCT is not an array function. It sounds like you have an #N/A in of 'Working Copy'!K3:K1038. &...

Split data into new sheets
I have a (very) long list, sorted by account code. I would like to write a macro that splits the list into separate sheets in the workbook, with a separate sheet for each account code. Ideally, I would also like to rename each sheet to show which account code the sheet contains. I have no idea, though, where to start. Any ideas? Thanks in advance. Hi bernard How many different accounts are in the list (more or less than 250)??? below some code that I use to split files by account numbers where the user has to select a cell within the column that contains the account number... Hope thi...

refer to whole column
Hi! How do I refer to a whole column by a number of a cell? For example, if a number of a certain cell is 1, then the column will be A:A. If a number of a cell is 3, the column will be C:C. I will be using the result as the [Sum_Range] in a SUMIF function. Thank you very much! =sumif(x:x,"xxx",indirect("C"&z999,false)) Where x:x contains the value you want to compare. And z999 contains the column number Ken wrote: > > Hi! > > How do I refer to a whole column by a number of a cell? For example, if a > number of a cert...

column format won't change???
After copying numerous sheets of dates where the format was (m/d/yy) 1/1/03, into a master sheet - i now want to change the format to (m/d/yyyy) 1/1/2003. But the column format won't change, even if i just change the format on a single cell. The format only changes if you "edit" the cell. You dont even have to change anything, just double-click and then click out, and it changes to the yyyy format - I'd do it by hand, but i have about 11000 records to change. right now im hitting F2 and Enter about a million times as they change 1 at a time. Any suggestions? thx ...

Excel Row and Column Headers
Help! For some reason, the Row and Header values have changed in existing and new Excel files. Instead of the Headers being listed alphabetically, they are now appearing numerically. Anyone have any ideas? ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ Go to 'Tools', 'Options' and then the general tab and deselect the R1C1 reference check box. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ Vie...

all long column of cells equal to whatever I put in B2
For some reason I am getting nowhere trying to do a very simple thing. I have a column with many of the cells for a long stretch that I want to be equal to whatever number I put in B2. As I'm building this spreadsheet, it seems to me that it should be a very simple matter to "tell" all those cells to just be equal to whatever is in B2, they using format painter, etc.,.... they don't copy "=B2" part of the previous cells, but instead just copy whatever the number is .... which means that when I change the number in B2, it won't change them. Any ideas? th...

Change column name??????
:rolleyes: *Can somebody help my out?????? I would like to know how to change the column name (A, B, C etc) into a different name...* --- Message posted from http://www.ExcelForum.com/ Good question BH 79 , I also would really like to know this and can't find anything about it :confused:. But I have seen docs in which the columns have names instead of the usual a,b,c-labels... Hope someone knows the answer... --- Message posted from http://www.ExcelForum.com/ "BH79 >" <<BH79.zrnhc@excelforum-nospam.com> wrote in message news:BH79.zrnhc@excelforum-nospam.com......

split column
I have a column of data in the following format column A John B. Smith What I want to do is split this column into three separate column so that it will appear as below Column B Column C Column D John B. Smith. Is there a formula that can help me do this. TIA Tlee, If all the names follow the same format (you don't say), you can use Data - Text to columns, using a space as the separator. Make sure that the columns to the right are empty before you start. -- Earl Kiosterud mvpearl omitthisword at verizon period net ---------------------------...

Adding totals in columns
I have a worksheet that we have hidden rows in. I want to find the sum of a column without picking up the hidden row values? I can I get a sum without including the hidden cell values? What version of Excel are you using? If you're using Excel 2003 or later try this: =SUBTOTAL(109,A1:A10) -- Biff Microsoft Excel MVP "Tanya" <Tanya@discussions.microsoft.com> wrote in message news:3797F36F-B648-469F-8F7B-6E07E5DC837F@microsoft.com... >I have a worksheet that we have hidden rows in. I want to find the sum of >a > column without picking ...

Average time from date column?
I have a column that is in general date format, 6/19/2007 5:34:23 PM. In another column I have a value between 10 and around 50. What I have to do is get the average of this number by time in a day. So if the number is 10 all day, then the average is 10. If the number is changed during the day, say its 5 for half the day then 10 for the other half of the day, then the number I'm looking for is 7.5. If the number is 5 for 1/4 of the day and 10 for the other 3/4 of the day then the number would be around 8.5..... if you follow what I'm trying to say. So far I have been a...

How can I line up 2 columns with similar information?
I have 2 columns with names in each and I'd like to line them up next to each other. I think we'll need more information, Kathy. I don't understand what you're trying to do. Can you give exactly what's up? ************ Anne Troy www.OfficeArticles.com "Kathy" <Kathy@discussions.microsoft.com> wrote in message news:A5C0BC96-7E27-4C55-89A3-03513FC0D409@microsoft.com... >I have 2 columns with names in each and I'd like to line them up next to >each > other. Column A has employee number, Column B has the same employee numbers but has addi...

Printing two columns of data in telephone directory style
Hi, Wondering if anyone can please help? I have a long (15,000 entry) list of data in two columns that I need to print. To save space, paper and access time, I'd like to print this list in two sets of two columns - as the names and numbers in a telephone directory might appear. What I envisage is my list starting at the top left of page 1, with its corresponding data column just to the right. The list would go down to the bottom of that page and then restart at the top of the page, just right of centre. Subsequent pages would continue in similar fashion. I have trie...