Excel Problem

[arv1111]

Hi guys
I need some help with one of my files.

Cell A1 shows the following information:-
Xantra Miggie
24 Boku Street
Palm Beach
Abcccc 21500
United States
Tel: (00)00000000

1. How do i leave just the name in cell A and move the other info from 24 Boku Street to Cell B1. I need it to perform this task until all the info. is captured.

2. After moving the info to Cell B1, how do i remove the country United States from the cell and also remove the cell spaces so that the Telephone number moves upward.
I also need it to perform this task until all the info. is captured.

Example of how it looks like after moving the info to Cell B1.

24 Boku Street
Palm Beach
Abcccc 21500

Tel: (00)00000000


Thanks in advance if anyone can help out.
Regards,
arv1111

 

[Gavona]

Two stages.
First get the data into columns
Then into rows.

Copy the newline character from one of the cells
Use Data,TextToColumns...paste from clipboard your delimiter
Delete the Country column....


Gavin

 

[arv1111]

Hi Gavona
Thanks for the advice.
Is there an alternative method?
Thanks.
Regards,
arv1111

 

[SkipVought]

It is the BEST method. Once you have data in proper columns, it is much easier to report and analyze.

Skip,

_{Just traded in my old subtlety...
for a brand NUANCE!}

 

[arv1111]

Hi guys
Tried it but didnt work. Not sure if i missed something.

This address is all in one cell. Say A1.
24 Boku Street
Palm Beach
Abcccc 21500

Tel: (00)00000000

When tried to convert text to column, it only picks up the first line - 24 Boku Street.

Thanks.
Regards,
arv1111

 

[combo]

Use vba, UDF function, in standard module:

Public Function Split2Row(txt As String, iRow As Integer)
Dim v
v = Split(txt, vbLf)
Select Case True
Case iRow <= 0
    Split2Row = UBound(v) + 1
Case iRow > UBound(v) + 1
    Split2Row = ""
Case Else
    Split2Row = v(iRow - 1)
End Select
End Function

=Split2Row(A1,0) returns number of rows in cell (divided with ALT+ENTER).
=Split2Row(A1,i) returns i-th row or empty string

combo

 

[SkipVought]

Did you try replacing the control character with, for instance, a semicolon? You can use alt+0010 (using the number pad) as the FIND character and semicolon as the REPLACE character. Then its quick Data > Text to columns.

Skip,

_{Just traded in my old subtlety...
for a brand NUANCE!}