Puzzled by move part of text from one cell to another

[Garetht2014]

[TABLE="width: 847"]
<colgroup><col width="64" style="width: 48pt;"> <col width="688" style="width: 516pt; mso-width-source: userset; mso-width-alt: 25161;"> <col width="89" style="width: 67pt; mso-width-source: userset; mso-width-alt: 3254;"> <col width="288" style="width: 216pt; mso-width-source: userset; mso-width-alt: 10532;"> <tbody>[TR]
[TD="width: 64, bgcolor: transparent"]
Hi,

Below is part of a table that I am trying to move data around on but really struggling as you can tell from this post lol. the Red text explains what I am trying to do.

Hope someone can help.

Thanks

Gareth














[/TD]
[TD="width: 688, bgcolor: transparent"]I need this column (B) to stay in the same format as it is now, but need to match the name from this column (A) to the name in Column D and put the name from column C at the beginning of Column B's name like the example below which has the Column C name at the start of the Column B name.[/TD]
[TD="width: 89, bgcolor: transparent"][/TD]
[TD="width: 288, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Example: SCIL P6 OLD TOWN N49 54.791 W6 17.962 (29U 693882 5532475)[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]-----------------------------------------------------------------------------[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]OLD TOWN N49 54.791 W6 17.962 (29U 693882 5532475)[/TD]
[TD="bgcolor: transparent"]SCIL P6[/TD]
[TD="bgcolor: transparent"]OLD TOWN[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]AA Exch lc@1[/TD]
[TD="bgcolor: transparent"]HIGH STREET[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]-----------------------------------------------------------------------------[/TD]
[TD="bgcolor: transparent"]AA P10@1[/TD]
[TD="bgcolor: transparent"]GLANDARE RD JCN BEECHWOOD AVE[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]THE PARADE ALL FAULTYS ARE FAULTY 21/9/94 WWSXM03 N49 54.877 W6 18.904 (29U 692750 5532594)[/TD]
[TD="bgcolor: transparent"]AA P11@1[/TD]
[TD="bgcolor: transparent"]JCN GADLYS RD & HIRWAUN ROAD[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]AA P12@1[/TD]
[TD="bgcolor: transparent"]O/S YNYSLWYD SCHOOL CARDIFF RD[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]-----------------------------------------------------------------------------[/TD]
[TD="bgcolor: transparent"]AA P13@1[/TD]
[TD="bgcolor: transparent"]S/O NO 12 BRYNMAIR RD[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]R/O THE STRAND N49 54.940 W6 18.697 (29U 692993 5532720)[/TD]
[TD="bgcolor: transparent"]AA P14@1[/TD]
[TD="bgcolor: transparent"]JCTN FFORCHAMAN AND CWMNEOL ST[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]AA P15@1[/TD]
[TD="bgcolor: transparent"]ROSE ROW ESTATE[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]-----------------------------------------------------------------------------[/TD]
[TD="bgcolor: transparent"]AA P16@1[/TD]
[TD="bgcolor: transparent"]S/O 18 CANAL RD[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]PARTING CARN N49 55.112 W6 18.025 (29U 693786 5533067)[/TD]
[TD="bgcolor: transparent"]AA P17@1[/TD]
[TD="bgcolor: transparent"]O/S NO 2 WELL PLACE[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]AA P18@1[/TD]
[TD="bgcolor: transparent"]JCN FOTHERGILL ST AND ABERNANT RD[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]-----------------------------------------------------------------------------[/TD]
[TD="bgcolor: transparent"]AA P19@1[/TD]
[TD="bgcolor: transparent"]JCN BEDFORD ST AND CARDIFF ROAD[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]ISLES OF SCILLY SCILLONIA N49 55.707 W6 18.184 (29U 693556 5534163)[/TD]
[TD="bgcolor: transparent"]AA P1@1[/TD]
[TD="bgcolor: transparent"]CARDIFF ROAD/CWMAMAN ROAD[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]AA P20@1[/TD]
[TD="bgcolor: transparent"]OPP BRYNAWEL MERTHYR RD LLWYDCOED[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]-----------------------------------------------------------------------------[/TD]
[TD="bgcolor: transparent"]AA P21@1[/TD]
[TD="bgcolor: transparent"]O/S 48 HARRIET ST TRECYNON[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]TELEGRAPH ROAD N49 55.709 W6 18.188 (29U 693550 5534166)[/TD]
[TD="bgcolor: transparent"]AA P22@1[/TD]
[TD="bgcolor: transparent"]O/S 15 CEMETERY RD TRECYNON[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]AA P23@1[/TD]
[TD="bgcolor: transparent"]GLOUSTER HSE DEAN ST[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]-----------------------------------------------------------------------------[/TD]
[TD="bgcolor: transparent"]AA P24@1[/TD]
[TD="bgcolor: transparent"]S/O CHURCH UNITY ST[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]TRESCO M.D.F. N49 57.412 W6 20.536 (29U 690631 5537221)[/TD]
[TD="bgcolor: transparent"]AA P25@1[/TD]
[TD="bgcolor: transparent"]OPP 5 GRIFFITH ST[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]AA P26@1[/TD]
[TD="bgcolor: transparent"]JCN LLWYD COED RD AND MERTHYR RD[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]-----------------------------------------------------------------------------[/TD]
[TD="bgcolor: transparent"]AA P27@1[/TD]
[TD="bgcolor: transparent"]MEIRION STREET[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]SENNEN N50 04.578 W5 41.212 (30U 307757 5550572)[/TD]
[TD="bgcolor: transparent"]AA P28@1[/TD]
[TD="bgcolor: transparent"](R'O 1 LAUREL CL)LABURNAM DRIVE.[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]AA P29@1[/TD]
[TD="bgcolor: transparent"]MAESGWYN[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]-----------------------------------------------------------------------------[/TD]
[TD="bgcolor: transparent"]AA P2@1[/TD]
[TD="bgcolor: transparent"]MOUNT HILL ST JUNC 274/275 CARDIFF ROAD[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]A30 N50 04.582 W5 41.227 (30U 307740 5550581)[/TD]
[TD="bgcolor: transparent"]AA P30@1[/TD]
[TD="bgcolor: transparent"]JCN KING STREET CWMDARE.[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]AA P32@1[/TD]
[TD="bgcolor: transparent"]JCN GADLYS STREET AND DOWLAIS ST[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]-----------------------------------------------------------------------------[/TD]
[TD="bgcolor: transparent"]AA P33@1[/TD]
[TD="bgcolor: transparent"]S/O 81 GLANAMAN RD[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]SENNEN COVE N50 04.664 W5 42.127 (30U 306673 5550770)[/TD]
[TD="bgcolor: transparent"]AA P34@1[/TD]
[TD="bgcolor: transparent"]S/O 11 STATION TCE[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]AA P35@1[/TD]
[TD="bgcolor: transparent"]R/O 115 JUBILEE ROAD GODREAMAN[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]AA P36@1[/TD]
[TD="bgcolor: transparent"]R/O CHAPEL PLEASANT VIEW ST ABERAMAN[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]AA P37@1[/TD]
[TD="bgcolor: transparent"]JCN CARDIFF RD AND BEDDOE ST ABERAMAN[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]AA P38@1[/TD]
[TD="bgcolor: transparent"]JCN CARDIFF RD AND CURRIE ST[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]AA P39@1[/TD]
[TD="bgcolor: transparent"]O/S CARREERS OFFICE WI ND ST[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]AA P3@1[/TD]
[TD="bgcolor: transparent"]CARDIFF STREET JUNCTIO N CROSS STREET[/TD]

[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]AA P41@1[/TD]
[TD="bgcolor: transparent"]JCN DERWENT DRIVE AND LLANGORSE RD[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]AA P42@1[/TD]
[TD="bgcolor: transparent"]S/O BAKERS GARAGE[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]AA P43@1[/TD]
[TD="bgcolor: transparent"]ABERAMAN INDUSTRIAL ESTATE[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]AA P44@1[/TD]
[TD="bgcolor: transparent"]JNC. CANAL RD & TIMOTHY ROW[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]AA P45@1[/TD]
[TD="bgcolor: transparent"]OPP EXCH HIGH ST[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]AA P46@1[/TD]
[TD="bgcolor: transparent"]S/O A.T.E.YARD HIGH STREET[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]AA P47@1[/TD]
[TD="bgcolor: transparent"]JCN.WHITCOMBE ST & CANON STREET[/TD]
[/TR]
</tbody>[/TABLE]

 

[konew1]

Is this what you mean?

Excel Workbook
	A	B	C
1	Example: SCIL P6 OLD TOWNN49 54.791 W6 17.962 (29U 693882 5532475)
2
3	SCIL P6 OLD TOWN N49 54.791 W6 17.962 (29U 693882 5532475)	OLD TOWN N49 54.791 W6 17.962 (29U 693882 5532475)	SCIL P6
Sheet1

 

[Garetht2014]

Thanks for getting back to me.

It is but there is a twist with this one always!!

The column C is not in any type of order, so I need to lookup the name in column A in the column C list, and then pull out the name from column B and insert in the beginning of column A.

So I lookup up OLD TOWN (Name from column A) in the column C's list, then I need to copy the corresponding name in column B (in this case SCIL P6) and add to the beginning of column A to make: SCIL P6 OLD TOWN N49 54.791 W6 17.962 (29U693882 5532475). The column A list as to stay in the same format in regards to the layout.

 

[konew1]

Hi You are puzzled trying to find the solution and I am puzzled trying to find the question.
It seems to me to be a relatively easy =INDEX MATCH solution. Your first posting includes column D, but no mention later. Please be clear about what data is in each column and what column you want the solution

If the cell in column A has OLD TOWN N49 54.791 W6 17.962 (29U693882 5532475) as text and you want it to show SCIL P6 OLD TOWN N49 54.791 W6 17.962 (29U693882 5532475) then you will need a vba solution. If the cell in A has a formula that returns OLD TOWN N49 54.791 W6 17.962 (29U693882 5532475). then you can have a formula solution by modifying the existing formula