Need Excel Expert:-It's very urgent

[kashif.special2005]

Hi,

I have value in Column A like below

A1 Callan Owen
A2 Phone:**016269921
A3 Address:**351 Landen Rd, DUBLIN 10, DUBLIN, D10
A4 Callan A
A5 Phone:**014531727
A6 Address:**60 Bangor Rd, DUBLIN 12, DUBLIN, D12
A7 Callan Peter
A8 Phone:**014502538
A9 Address:**7 St Gerards Rd, DUBLIN 12, DUBLIN, D12

I want output like this

Name Phone Address
Callan Owen 016269921 51 Landen Rd, DUBLIN 10, DUBLIN, D10
Callan A 014531727 60 Bangor Rd, DUBLIN 12, DUBLIN, D12
Callan Peter 014502538 7 St Gerards Rd, DUBLIN 12, DUBLIN, D12


Please help.

Thanks & Regards,
Kashif.

 

[kashif.special2005]

Sorry, I forget to mentioned one point that data in A column till A1 to A5000
in Three sets like A1 to A3 one set name,phone number and address
A4 to A6 second set name,phone number and address, and so on.

Thanks,
Kashif.

 

[MickG]

Try this :-
Results in column "B".

[COLOR="Navy"]Sub[/COLOR] MG30May52
[COLOR="Navy"]Dim[/COLOR] Lst [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
Lst = Range("A" & rows.Count).End(xlUp).row
    [COLOR="Navy"]For[/COLOR] n = 1 To Lst [COLOR="Navy"]Step[/COLOR] 3
        [COLOR="Navy"]With[/COLOR] Range("A" & n)
            c = c + 1
            Cells(c, 2) = Replace(Replace(Replace(Replace(Join(Application.Transpose(.Resize(3).value)), "Phone", ""), "Address", ""), "*", ""), ":", "")
        [COLOR="Navy"]End[/COLOR] With
     [COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[kashif.special2005]

I am sorry but it is not working

I want that name would be in Column B, Phone number would be Column C and Address would be in Column C.

Like below.


Name Phone Address
Callan Owen 016269921 51 Landen Rd, DUBLIN 10, DUBLIN, D10
Callan A 014531727 60 Bangor Rd, DUBLIN 12, DUBLIN, D12
Callan Peter 014502538 7 St Gerards Rd, DUBLIN 12, DUBLIN, D12


Thanks,
Kashif.

 

[SydneyGeek]

In B1: =IF(OR(LEFT(A1,5)="Phone",LEFT(A1,7)="address"),"",MID(A2,9,12))
In C1: =IF(OR(LEFT(A1,5)="Phone",LEFT(A1,7)="address"),"",MID(A3,11,40))

Fill down.
Then copy the whole range, paste special / values, and filter.
Clean out blanks in column B.

Denis

 

[jim may]

Will this work for you? Copy down B1:D1

Excel Workbook
	A	B	C	D
1	Callan Owen	Callan Owen	016269921	351 Landen Rd, DUBLIN 10, DUBLIN, D10
2	Phone:**016269921	Callan A	014531727	60 Bangor Rd, DUBLIN 12, DUBLIN, D12
3	Address:**351 Landen Rd, DUBLIN 10, DUBLIN, D10	Callan Peter	014502538	7 St Gerards Rd, DUBLIN 12, DUBLIN, D12
4	Callan A
5	Phone:**014531727
6	Address:**60 Bangor Rd, DUBLIN 12, DUBLIN, D12
7	Callan Peter
8	Phone:**014502538
9	Address:**7 St Gerards Rd, DUBLIN 12, DUBLIN, D12
Sheet1

Excel 2007

Cell Formulas
Range	Formula
B1	=INDIRECT("$A"&ROW()*3-2)
C1	=MID(INDIRECT("$A"&ROW()*3-1),9,99)
D1	=MID(INDIRECT("$A"&ROW()*3),11,99)

 

[Sektor]

Sub Callan()
    
    ' Tools -> Reference -> Microsoft VBScript Regular Expressions 5.5

    Dim i As Long, j As Long
    Dim re As New RegExp, sh As Worksheet
    Dim arr1() As Variant, arr2() As Variant, arr3() As Variant
    
    For i = 1 To 5000
    
        If Cells(i, 1) Like "Callan* Then
            j = j + 1
        End If
        
        Select Case True
            Case Cells(i, 1) Like "Callan*"
                ReDim Preserve arr1(1 To j)
                re.Pattern = "Callan"
                arr1(i) = re.Replace(Cells(i, 1), vbNullString)
            Case Cells(i, 1) Like "Phone*"
                ReDim Preserve arr2(1 To j)
                re.Pattern = "Phone:"
                arr2(i) = re.Replace(Cells(i, 1), vbNullString)
            Case Cells(i, 1) Like "Address*"
                ReDim Preserve arr3(1 To j)
                re.Pattern = "Address:"
                arr3(i) = re.Replace(Cells(i, 1), vbNullString)
        End Select

    Next

    Set sh = Sheets.Add(After:=Sheets(Sheets.Count))
    With sh
        For i = 1 To UBound(arr1)
            .Cells(i, 1) = arr1(i) & " " & arr2(i) & " " & arr3(i)
        Next
    End With
    
End Sub

 

[kashif.special2005]

Hi,

Thanks for reply both formula are working, but there is one problem.

I am sorry, I did not see below some where there is false name above phone number like.These names are not fix may be any names
DENTAL SURGEONS*
REFLEXOLOGY*




MacNally Dr TC BDS NUI
DENTAL SURGEONS*
Phone:**014542984
Address:**144 Sundrive Rd, DUBLIN 12, DUBLIN, D12

McPartlan Geraldine
REFLEXOLOGY*
Phone:**018308480
Address:**11 Claremount Ct, DUBLIN 9, DUBLIN, D9


Thanks,
Kashif.

 

[kashif.special2005]

Hi Sektor,

VBA Code is not working, there is an error message showing

user-defined type not find.

Thanks,
Kashif.

 

[Sektor]

I wrote reference:

' Tools -> Reference -> Microsoft VBScript Regular Expressions 5.5