Search and replace

[bhupeshgrover]

Hi, I am trying to search a text from a field and trying to copy it in next field. e.g the text appearing in A33 needs to be searched from b1:b32 and if found copy the entire text appearing in that filed to b33.

is there is any VBA availabe.

 

[jtakw]

Hi,

If I understand correctly, a formula can do that, if interested, show a few lines of sample data.

 

[bhupeshgrover]

Thanks, here is some data


[TABLE="width: 892"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Data 1[/TD]
[TD]Data 2[/TD]
[/TR]
[TR]
[TD]M170000150 [/TD]
[TD]M170000150/M170000308[/TD]
[/TR]
[TR]
[TD]M170000156[/TD]
[TD]M170000156/M170000415[/TD]
[/TR]
[TR]
[TD]M170000172[/TD]
[TD]M170000172/M170000389[/TD]
[/TR]
[TR]
[TD]M170000188[/TD]
[TD]M170000188[/TD]
[/TR]
[TR]
[TD]M170000194[/TD]
[TD]M170000194[/TD]
[/TR]
[TR]
[TD]M170000212[/TD]
[TD]M170000212/M170000299/MF17000313/MF17000314/MF17000315/MF17000316/MF17000317/MF17000267/M180000190/M180000191[/TD]
[/TR]
[TR]
[TD]M170000221[/TD]
[TD]M170000221[/TD]
[/TR]
[TR]
[TD]M170000226[/TD]
[TD]M170000226[/TD]
[/TR]
[TR]
[TD]M170000230[/TD]
[TD]M170000230[/TD]
[/TR]
[TR]
[TD]M170000233[/TD]
[TD]M170000233/234/M170000485-488/490-499/507to509/511/512/517/520/M180000021/30[/TD]
[/TR]
[TR]
[TD]M170000234[/TD]
[TD]M170000233/234/M170000485-488/490-499/507to509/511/512/517/520/M180000021/30[/TD]
[/TR]
[TR]
[TD]M170000236[/TD]
[TD]M170000236[/TD]
[/TR]
[TR]
[TD]M170000245[/TD]
[TD]M170000245/MF17000204/MF17000412[/TD]
[/TR]
[TR]
[TD]M170000251[/TD]
[TD]M170000251/M180000085[/TD]
[/TR]
[TR]
[TD]M170000253[/TD]
[TD]M170000253/MF17000272/MF170000268[/TD]
[/TR]
[TR]
[TD]M170000172[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]M170000172[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MF17000313

[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
M180000190

 

[jtakw]

Thanks for sample data.

So hopefully I understand your query correctly.
In my sample below, B18 formula copied down.
Notice B16 is Not an exact match to A19, since B16 has an extra digit ( 1 ) at the end, if this Should be considered a Match, we can easily adjust the formula:

Book1
	A	B
1	Data 1	Data 2
2	M170000150	M170000150/M170000308
3	M170000156	M170000156/M170000415
4	M170000172	M170000172/M170000389
5	M170000188	M170000188
6	M170000194	M170000194
7	M170000212	M170000212/M170000299/MF17000313/MF17000314/MF17000315/MF17000316/MF17000317/MF17000267/M180000190/M180000191
8	M170000221	M170000221
9	M170000226	M170000226
10	M170000230	M170000230
11	M170000233	M170000233/234/M170000485-488/490-499/507to509/511/512/517/520/M180000021/30
12	M170000234	M170000233/234/M170000485-488/490-499/507to509/511/512/517/520/M180000021/30
13	M170000236	M170000236
14	M170000245	M170000245/MF17000204/MF17000412
15	M170000251	M170000251/M180000085
16	Not a match->	M1700001711
17	M170000253	M170000253/MF17000272/MF170000268
18	M170000172	M170000172/M170000389
19	M170000171	No Match
20	M170000172	M170000172/M170000389
21	MF17000313	M170000212/M170000299/MF17000313/MF17000314/MF17000315/MF17000316/MF17000317/MF17000267/M180000190/M180000191
22	M180000190	No Match
Sheet531

Cell Formulas
Range	Formula
B18	=IFERROR(LOOKUP(2,1/SEARCH("/"&A18&"/","/"&B$2:B$17&"/"),B$2:B$17),"No Match")

 

[jtakw]

Just noticed there was a Trailing Space in A22 and thrown off the result, removed trailing space for correct result:

Book1
	A	B
1	Data 1	Data 2
2	M170000150	M170000150/M170000308
3	M170000156	M170000156/M170000415
4	M170000172	M170000172/M170000389
5	M170000188	M170000188
6	M170000194	M170000194
7	M170000212	M170000212/M170000299/MF17000313/MF17000314/MF17000315/MF17000316/MF17000317/MF17000267/M180000190/M180000191
8	M170000221	M170000221
9	M170000226	M170000226
10	M170000230	M170000230
11	M170000233	M170000233/234/M170000485-488/490-499/507to509/511/512/517/520/M180000021/30
12	M170000234	M170000233/234/M170000485-488/490-499/507to509/511/512/517/520/M180000021/30
13	M170000236	M170000236
14	M170000245	M170000245/MF17000204/MF17000412
15	M170000251	M170000251/M180000085
16	Not a match->	M1700001711
17	M170000253	M170000253/MF17000272/MF170000268
18	M170000172	M170000172/M170000389
19	M170000171	No Match
20	M170000172	M170000172/M170000389
21	MF17000313	M170000212/M170000299/MF17000313/MF17000314/MF17000315/MF17000316/MF17000317/MF17000267/M180000190/M180000191
22	M180000190	M170000212/M170000299/MF17000313/MF17000314/MF17000315/MF17000316/MF17000317/MF17000267/M180000190/M180000191
Sheet531

Cell Formulas
Range	Formula
B18	=IFERROR(LOOKUP(2,1/SEARCH("/"&A18&"/","/"&B$2:B$17&"/"),B$2:B$17),"No Match")

 

[bhupeshgrover]

Thanks, that was a great help. can you help in explaining the formula pls.

 

[jtakw]

You're welcome.

The formula searches the string in Ax within Column Bx:Bx, adding a slash fore and aft of Ax and Bx:Bx to avoid incorrect matches as in my sample A19 and B16, when a match is found, return that match in Bx:Bx, if more than 1 match is found, will return the Last match in Bx:Bx, otherwise, No Match.

However, looking at your sample data further, if your Ax value is say, M170000485 or M170000486, where your B11 and B12 is M170000485-488, it will produce "No Match".