SPLIT LARGE TEXT INTO DIFFERENT COLUMN

[GOPALK]

Hi


Could you please help me how to split large text into different columns without breaking word and also the column width should not be more than 32 characters

for ex:

column A (ROW1): 52/1234 PADMAVATI FLATS BEHIND NEW FARIDA MARKET POONAM MARKET

column A (ROW2): 1567 TOPS RESTAURANT VISTA CORNER TP CROSSING

 

[oldchippy]

Hi,


Use Data > Text to Columns and for the delimiter use a space, each word will be in a separate cell. Is that what you want?

 

[GOPALK]

Hi chippy


If I go for delimiter by using a space that will create so many columns. If the original column filled with 60 characters, I want to split the text into two columns max of 32 characters for example in orginal text it is like this.


column A (ROW1): 52/1234 PADMAVATI FLATS BEHIND NEW FARIDA MARKET POONAM MARKET


After editing

A1 : 52/1234 PADMAVATI FLATS BEHIND

 

[oldchippy]

Hi,


OK, row A is =LEN(A1) 62 characters long, the longest of your two samples so in this case you could use in B1


=TRIM(LEFT(A1,LEN(A1)/2)) and in C1


=TRIM(MID(A1,LEN(A1)/2,32))


But for row 2 this splits the word VISTA with V in B2 and VISTA in C2


Are we getting closer to what you want?

 

[oldchippy]

Hi again,


I'm assuming you are using Excel 2007 so here's a solution using some helper columns


In B1 =IFERROR(FIND(" ",$A1,1),"")

In C2 =IFERROR(FIND(" ",$A1,B1+1),"") fill this across to say cell M1

In N1 =HLOOKUP(32,B1:M1,1)

In O1 =TRIM(LEFT(A1,N1))

In P1 =TRIM(MID(A1,N1,32))


Then select cells B11 and auto-fill down, then hide columns B:N

 

[GOPALK]

Hi,


We are really getting closer but for bigger data's we can't break the word.


In this function, can we add one morefunction of breaking the sentence where there is a space within 32 characters but not to take all spaces into account. for ex;


1567 TOPS RESTAURANT VISTA CORNER TP CROSSING

break will be between RESTAURANT / VISTA

 

[Hui]

Gopalk

Without a defined set of rules you could try something like

in B1 put

=LEFT(A1,FIND(" ",A1,LEN(A1)/4))

in B2 put =RIGHT(A1,LEN(A1)-LEN(B1))

It will find the first space after 1/4 the length and separate at that point

 

[oldchippy]

Hi,


We could do with a comma (or something like that) in the line to define where you want the break to be

 

[GOPALK]

Thanks a lot Mr.Hui. It's a perfect and marvelus solution and saved lots of time. This is what I required.


Thanks once again


Gopalk

 

[ilayaraja]

Hi,

I have different address,how to i spilit the country using excel

Old No 79, New No 10, Perambur Chennai Tamil Nadu India

12 Brownlee Crescent Wheelers Hill Melbourne Victoria Australia

5712 BRIDGE CREEK AVE BAKERSFIELD CA United States..

 

[NARAYANK991]

Hi Ilayaraja ,


I do not know whether it is possible to separate the country name from the text strings you have posted , since the length of the text strings varies , and so does the number of words in both the text strings , and the names of countries e.g. India and Australia are one word each , while United States is two words.


What you can do is have a list of the possible countries in a list which you can name Countries ; suppose your range of addresses is also a named range Addresses , then you can use the following formula to get the names of the countries within the addresses :


=LOOKUP(99,FIND(Countries&".",A2&"."),Countries)


I have assumed your list of addresses starts from cell A2 , and goes downwards ; put this formula in any unused cell , say B2 , and copy downwards.


A similar question was asked in this forum earlier to which the above answer was posted , in this link :


http://chandoo.org/forums/topic/find-a-list-of-words-in-a-cell


Narayan