Splitting Text from single cell in column across multiple Columns

Text To column wont work because I have name and address info in a single 
cell in this format. Each cell has different data representing different 
addresses.

Name
St # St Name
Phone Number
City, State zip

I want a formula or something to take first line and put in one column, the 
2nd line in another column and the 3rd line spread across 3 columns. Although 
if you could just find a way to split each line into a column, that would 
work. 

Once again, Text to colun won't work, I have multiple cells with diffeent 
addresses, they are not all the same and there are no delimiters. 
0
Utf
3/12/2010 9:53:02 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

3 Replies
1244 Views

Similar Articles

[PageSpeed] 8

Harold- 

it may take a few steps, put you can parse your "big" string using left() 
and right()

A1 = your cell contents ("big" string)
The linewrap in the cell is most likely char(10), in which case you can use
B1=left(A1,find(char(10),A1)-1)
then you want to shorten your "big" string to only what is left, so you can 
do it again:
C1 = right(A1,(len(A1)-Len(B1))-1)
rinse and repeat for your next 3 columns

If you want to split your phone number out into 3 columns, do you have a 
standard format? If so, use that standard format to determine how many digits 
to split out (you won't need the count function). For example, if it is (123) 
456-7890 [spaces intentional] then you might use:
W1 = your number
X1 = mid(W1,2,3)
Y1 = mid(W1,7,3)
Z1 = mid(W1,11,4)

HTH,
Keith

"Harold" wrote:

> Text To column wont work because I have name and address info in a single 
> cell in this format. Each cell has different data representing different 
> addresses.
> 
> Name
> St # St Name
> Phone Number
> City, State zip
> 
> I want a formula or something to take first line and put in one column, the 
> 2nd line in another column and the 3rd line spread across 3 columns. Although 
> if you could just find a way to split each line into a column, that would 
> work. 
> 
> Once again, Text to colun won't work, I have multiple cells with diffeent 
> addresses, they are not all the same and there are no delimiters. 
0
Utf
3/13/2010 12:11:02 AM
You can use Text To Columns to split the separate lines of text out to 
individual columns... just uncheck any checked CheckBoxes and then select 
the CheckBox labeled "Other", click into its empty field and key-in Ctrl+J 
(which this the key-in for a New Line character)... you won't see anything 
happen in the field you key it into to, but look down at the chart and you 
will see the lines of text were split apart.

-- 
Rick (MVP - Excel)


"Harold" <Harold@discussions.microsoft.com> wrote in message 
news:3541D386-66DE-436D-BCA0-F1E9FCF5E078@microsoft.com...
> Text To column wont work because I have name and address info in a single
> cell in this format. Each cell has different data representing different
> addresses.
>
> Name
> St # St Name
> Phone Number
> City, State zip
>
> I want a formula or something to take first line and put in one column, 
> the
> 2nd line in another column and the 3rd line spread across 3 columns. 
> Although
> if you could just find a way to split each line into a column, that would
> work.
>
> Once again, Text to colun won't work, I have multiple cells with diffeent
> addresses, they are not all the same and there are no delimiters. 

0
Rick
3/13/2010 6:51:54 PM
Hi,

Once can also input Alt+010 (on the numeric keypad) instead of Ctrl+J

-- 
Regards,

Ashish Mathur
Microsoft Excel MVP

"Rick Rothstein" <rick.newsNO.SPAM@NO.SPAMverizon.net> wrote in message 
news:etnTG5twKHA.5812@TK2MSFTNGP02.phx.gbl...
> You can use Text To Columns to split the separate lines of text out to 
> individual columns... just uncheck any checked CheckBoxes and then select 
> the CheckBox labeled "Other", click into its empty field and key-in Ctrl+J 
> (which this the key-in for a New Line character)... you won't see anything 
> happen in the field you key it into to, but look down at the chart and you 
> will see the lines of text were split apart.
>
> -- 
> Rick (MVP - Excel)
>
>
> "Harold" <Harold@discussions.microsoft.com> wrote in message 
> news:3541D386-66DE-436D-BCA0-F1E9FCF5E078@microsoft.com...
>> Text To column wont work because I have name and address info in a single
>> cell in this format. Each cell has different data representing different
>> addresses.
>>
>> Name
>> St # St Name
>> Phone Number
>> City, State zip
>>
>> I want a formula or something to take first line and put in one column, 
>> the
>> 2nd line in another column and the 3rd line spread across 3 columns. 
>> Although
>> if you could just find a way to split each line into a column, that would
>> work.
>>
>> Once again, Text to colun won't work, I have multiple cells with diffeent
>> addresses, they are not all the same and there are no delimiters.
> 
0
Ashish
3/14/2010 11:06:24 AM
Reply:

Similar Artilces:

Using variable names for cells
I seem to remember a technique where I could assign a variable name to the contents of a cell so that whenever I wanted to use the contents, all I had to do was call up the variable name. Unfortunately I cannot find the way to set up the process. Any suggestsions or ideas would be appreciated. Thanks and a Happy New Year. -- Take out the trash to reply '05 FLHTCUI Hi dim rng as range set rng=activesheet.range("A1") msgbox rng.value -- Regards Frank Kabel Frankfurt, Germany Ultraglide wrote: > I seem to remember a technique where I could assign a variable name to >...

Find MAX data in sheet (Cell)
How would one go about finding the cell that contains the MAX info, o say any/all cells that exceeds 8,000 characters/spaces in a Excel page -- confuzedagai ----------------------------------------------------------------------- confuzedagain's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2943 View this thread: http://www.excelforum.com/showthread.php?threadid=49148 1. Highlight the entire document 2. Select Format->Conditional Formatting 3. Select "Formula Is" 4. Input this formula =LEN(A1)>8000 5. Format with something obvious like green bac...

Average formula where blank cells are counted as zeros
I am trying to write an average formula that takes into account the blank cells. I had only cells A2, A4, A6 filled out, but when I do =average(A1:A6), it is not treating the blanks as zeros so the answer is much higher than it should be. Should I do a logic formula within the cells? If you know the range, you could do the division yourself: =sum(a1:a6)/6 But this would include other non-numeric cells in the count of cells (6), too. krwelling wrote: > > I am trying to write an average formula that takes into account the blank > cells. > > I ha...

changing numbers to text
Hi group, I have a long list of zip codes in column A that has been complied. the problem is that some are text and some are not. What I mean is that I "test" them with =ISTEXT(A1) for example, if they are text I get TRUE if not I get False. I want to convert all of them to Text so that I can export them to ACcess. I tried converting the ones that were not text by choosing the Text under Format menue/ Cells/ Number then Text but this doesn't convert it really. When I "test" it with =ISTEXT(), its still not a text. Any help is very much appreciated C. ...

Combining Landscape and Portrait pages in a single document
While it was not possible in Publisher 2003, is there a chance that it is now possible in Publisher 2007 to have a landscape page in a portrait document? ...

Cells not sorting right with other worksheets
I have some problems!!! First I have multiple worksheets in a workbook. And in one column that has some names. and the columns next to them has data which is yes/no go/ngo for each person. If I add a person it will update itself in each worksheet. But I need to sort it. So I sort it and it sorts fine in the worksheet I'm working in. But with the other worksheets it sorts it but the data for each person doesn't stay with the person... I hope I explained this problem to where someone can understand. PLEASE HELP!!! How do you have the referencews set between each worksheet? &qu...

hide hidden text from viewing
This is very confusing! Where is the option to turn off the viewing of hidden text in the document? I selected a number of hidden text and now I cannot turn it off so that others do not see it. I would recommend that your new version of Word includes something useful and easy to find in this regard! Thanks. There are (at least) two ways to unHide the Hidden Text: (1) with the Show Non-Printing Characters command (Ctrl-Shift-8, or the paragraph- symbol button on your toolbar or ribbon) and (2) a check-box in the View Options. If (2) is on, (1) won't override it. On ...

text date to date format
I have column filled with: 'Apr 3 2005 4:37PM And I need to change it to: 04/03/05 4:37PM How do I get a textual date column into a sortable date/time column? I'm having problems because it sorts everything by alpha then numeric. For example, I have "Apr 15, 2005" before "Aug 10, 2003". I need to get these sorted by year, month, day...not alphabetically. Thanks in advance for your help! you are right: the filter is not a time but a alpha sort you may change the cell format or copy the value in an other cell and put the new format in the new column. ...

How to find cells with links to other workbooks?
I have a spreadsheet which shows under the edit menu links to other workbooks. I have tried to find where the links are without success. Is there a easy wy of finding such links? Hi you may try http://oaltd.co.uk/MVP/Default.htm and search for Findlink.zip (http://oaltd.co.uk/DLCount/DLCount.asp?file=FindLink.zip) "Bathonian" wrote: > I have a spreadsheet which shows under the edit menu links to other > workbooks. I have tried to find where the links are without success. Is there > a easy wy of finding such links? ...

Summing cells in pivot tables
I have a problem summing cells in a pivot table. When I double-click on a cell that I want to add, the formula from that cell gets added. Eventually I run out of characters. Is there a way to switch how the cell gets picked up. I can manually enter the cell addresses that I am trying to sum but that takes forever. Thanks, Ted There are instructions here for adding the Generate GetPivotData button to a toolbar, and toggling the feature on and off: http://www.contextures.com/xlPivot06.html Ted wrote: > I have a problem summing cells in a pivot table. When I double-cli...

to extract multiple values from an array
Hi, I am into Image processing industry, for each job we create unique code in excel, we Process 20 jobs in a day, I want to list all the job Code in "summary of the day sheet" that we complete for the particular day . On Dec 30, 4:56=A0am, ratan h <ratha...@nextgenalbums.com> wrote: > Hi, > > I am into Image processing industry, for each job we create unique > code in excel, we Process 20 jobs in a day, I want to list all the job > Code in "summary of the day sheet" that we complete for the particular > day . Not enough info but, assuming you are...

Conditional format if cell=0 then font colour same as background .
I have linked a cell from another sheet. If the original cell is blank, 0 shows in the linked cell (text would be typed in source cell). To handle this, I conditionally formatted the linked cell, so if it equals zero, the font colour is the same as the background colour of the cell so you can't see the zero But the zero still prints even though it is not showing prior to printing Try the formula =IF(Sheet1!F14="","",Sheet1!F14) in the linked cell. HTH Sandy -- to e-mail direct replace @mailintor.com with @tiscali.co.uk "Paligap" <Paligap@discu...

OLXP AutoComplete: How to Delete Single Entry
Q289975 mentions "changing" a single entry, but not deleting it. Posts to this list on the same subject mention how to delete entries from the AutoComplete list, but those suggestions will not work to remove a single entry from the cache. Can anyone suggest a procedure to remove a single entry from the AutoComplete list that actually works? OLXP AutoComplete caches resolved addresses into the <profilename>.NK2 file. Deleting the NK2 file removes single instances but that is a rather extreme solution to the problem. Is there an editor that one can use to edit the NK2 file to re...

Refresh pivot chart when info in a cell changes
To all, I am looking for help with a Macro. What I ultimately want to do is refresh a pivot chart when the user makes a selection from a list (in a data validation cell). For example, I am an end user that selects my territory from a "drop down". The pivot chart will update to the information that is directly related to my territory. Thanks in advance for your help. Excel 2007, PivotTable With Table dependent drop-downs instead of data validation drop-downs. With non-event-driven macros. http://www.mediafire.com/file/ygzjqyi2j1x/03_09_10.xlsm ...

Am I able to manage multiple email accounts
I have several email accounts including Gmail, HOtmail and corporate. I can't make Entourage mail (2004) list individual accounts or find how to manage profiles. Apple mail works but doesn't allow me to insert a tiff company logo without making it an attachment. Outlook allows for all of the above.<br> Appreciate help > This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. --B_3285235088_9822952 Content-type: text/plain; charset="US-ASCII" Content-transfer-encoding: 7bit On 2/7/08 1...

Display value of cell
Using Excel 2000 In a cell the formula shows instead of the value/result, how do I correct this? I want to see the formula. Reply to kdfoxca@yahoo.com Thanks. Are all formulas cells show the formulas or just one? Maybe you have a space before the = (Excel think it is text now) Or your cell is format as text -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "Kenya" <anonymous@discussions.microsoft.com> wrote in message news:050b01c3942b$e28b7240$a301280a@phx.gbl... > Using Excel 2000 > In a cell the formula shows instead of the value/resul...

Adjusting Text box size
In Access 2007, after creating a form using the wizard, all of the text boxes created for entering data are the same size. When I try to adjust the width of one, they all change width. How can I unlock or change this feature? I have deleted a box, and then re-added it. This sometimes works -- however if you get the size close to the size of the other boxes, it “locks in” again. I appreciate all sincere answers. Ron ah yes....a new feature in 07... in Design View; select the 'Arrange' tab then on the left side of my ribbon is: Tabular Stacked Remove You want t...

Combine variable number of columns
I have a single spreadsheet with a list of clients, addresses and their product interests. This table will be used to drive a Mailmerge document. In the document, I want to be able to refer to the products in which they indicated an interest. The problem is that while one client may have identified only one product (one column) others have selected anywhere from two to 170 products - each product is in a separate column. I can join two columns with "&", but when I have an inconsistent number of columns how do I do this efficiently? - I will have to do it for each client, e...

Cell Reference #3
In a workbook, is it possible to know all the cells where a particular cell is referenced in a calculation. Specifically, if I make a change in one cell, what are all the cells that will be affected. ...

Maximum number of characters in a cell
Is there a way to format a cell so that it can contain more than 255 characters? Bill Hi Bill, > Is there a way to format a cell so that it can contain more than 255 > characters? A cell can contain about 32000 characters, but Excel will display only about a 1000 of them (depending on font and fontsize). Regards, Jan Karel Pieterse Excel MVP http://www.jkp-ads.com If you would add some forced (manual) line breaks (<Alt> <Enter>) in your text at opportune locations, you will find that you can *display* much more text. -- HTH, RD ----------------------------------...

merge cells with a twist
A B C joe @ domain.com trying to merge cells in a row so that column a(joe) b(@) c(domain.com) and that result is joe@domain.com Thank you. =A1&B1&C1 -- Regards, Peo Sjoblom (No private emails please) "jd" <jdumont@@novuscom.net> wrote in message news:uaAWelbgFHA.3436@tk2msftngp13.phx.gbl... >A B C > joe @ domain.com > > trying to merge cells in a row so that column a(joe) b(@) c(domain.com) > and > that result is joe@domain.com > Thank you. > > =hyper...

Deleting a word from a Cell
Hi Can anyone help, i am trying to deletel the last word from a Cell My cell currently read: - M04274059519 Total and I need a formula that will delete the "total" word Would really appreciate some help linexe On Jan 21, 7:27=A0am, Linexe <l.clark...@hotmail.co.uk> wrote: > Hi > > Can anyone help, i am trying to deletel the last word from a Cell > > My cell currently read: - =A0M04274059519 Total > > and I need a formula that will delete the "total" word > > Would really appreciate some help > > linexe C...

Configure single Outlook 2003 Account to try multiple outgoing servers?
Is there a way to configure a single Outlook 2003 account to try different SMTP servers for Outgoing mail? I log in from two different locations, and both are behind firewalls. And from each location I can't get to the other SMTP server to send my mail. To get around this, I've created two Outlook accounts to use. One for each location. But this is not a good solution, because I have to remember to use the correct account, and it's easy to make a mistake. ....When I do, the outgoing mail very quietly sits in my Outbox, and doesn't get sent. People get very upset at me for ...

Number to text conversion ......HELP
Dear Friend.. I need help in converting formula calculation ( Number or Currency ) into Text EQ. Result = 2,500.00 converting to .....two thousand five hundred.. Thanks Check http://www.mvps.org/access/modules/mdl0001.htm at "The Access Web" -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Eko Hery" <eko@megaenterprise.com> wrote in message news:BCD8B0CE.1D8%eko@megaenterprise.com... > Dear Friend.. > I need help in converting formula calculation ( Number or Currency ) into > Text > > EQ. Result = 2,500.00 con...

referring cell in macro function
If a create a macro intended to be used as a function in a worksheet is there a property of the referring cell? I want to know what cell the formula is in. Dim callCell As String callCell = Application.Caller.Address Returns the cell address of the cell where the UDF is used. "zxcv" <zxcvnosend@yahoo.com> wrote in message news:aef3cafd-4c52-40a6-8348-ebf957b3abad@a21g2000yqn.googlegroups.com... > If a create a macro intended to be used as a function in a worksheet > is there a property of the referring cell? > > I want to know what cell the form...