k0st4din
02-10-2014, 09:40 AM
Hello,
can anyone tell me and help whether this kind of formula can be replaced by macro and perform the same function. This is just an example, then I will change my columns.
The idea is to search in one column (chosen by me) and the string of text, if I find search terms to bring them back again for each row in another column.
For example, looking at column A2:A and returns in column B2:B
=REPLACE(REPT("+AF266";COUNTIF(A2;"*AF266*"))&REPT("+AF267";COUNTIF(A2;"*AF267*"))&REPT("+AF268";COUNTIF(A2;"*AF268*"))&REPT("+AF269";COUNTIF(A2;"*AF269*"))&REPT("+AF311";COUNTIF(A2;"*AF311*"))&REPT("+CF706";COUNTIF(A2;"*CF706*"))&REPT("+CF707";COUNTIF(A2;"*CF707*"))&REPT("+CF708";COUNTIF(A2;"*CF708*"))&REPT("+CF512";COUNTIF(A2;"*CF512*"))&REPT("+CF508";COUNTIF(A2;"*CF508*"))&REPT("+CF437";COUNTIF(A2;"*CF437*"))&REPT("+CF648";COUNTIF(A2;"*CF648*"))&REPT("+CF649";COUNTIF(A2;"*CF649*"))&REPT("+CF444";COUNTIF(A2;"*CF444*"))&REPT("+HF095";COUNTIF(A2;"*HF095*"))&REPT("+NF520";COUNTIF(A2;"*NF520*"))&REPT("+NF521";COUNTIF(A2;"*NF521*"))&REPT("+NF522";COUNTIF(A2;"*NF522*"))&REPT("+NF523";COUNTIF(A2;"*NF523*"))&REPT("+AF386";COUNTIF(A2;"*AF386*"));1;1;"")
If in A2 i have words: only fo example: Hdskdsd, jdsdsldd, AF267, jhsffjh, CF707, hfhfhff ffksdfksslAF268. -> Need to see: AF267+CF707+AF268 -> fo example in B2 and to the end.
Thanks in advance.
can anyone tell me and help whether this kind of formula can be replaced by macro and perform the same function. This is just an example, then I will change my columns.
The idea is to search in one column (chosen by me) and the string of text, if I find search terms to bring them back again for each row in another column.
For example, looking at column A2:A and returns in column B2:B
=REPLACE(REPT("+AF266";COUNTIF(A2;"*AF266*"))&REPT("+AF267";COUNTIF(A2;"*AF267*"))&REPT("+AF268";COUNTIF(A2;"*AF268*"))&REPT("+AF269";COUNTIF(A2;"*AF269*"))&REPT("+AF311";COUNTIF(A2;"*AF311*"))&REPT("+CF706";COUNTIF(A2;"*CF706*"))&REPT("+CF707";COUNTIF(A2;"*CF707*"))&REPT("+CF708";COUNTIF(A2;"*CF708*"))&REPT("+CF512";COUNTIF(A2;"*CF512*"))&REPT("+CF508";COUNTIF(A2;"*CF508*"))&REPT("+CF437";COUNTIF(A2;"*CF437*"))&REPT("+CF648";COUNTIF(A2;"*CF648*"))&REPT("+CF649";COUNTIF(A2;"*CF649*"))&REPT("+CF444";COUNTIF(A2;"*CF444*"))&REPT("+HF095";COUNTIF(A2;"*HF095*"))&REPT("+NF520";COUNTIF(A2;"*NF520*"))&REPT("+NF521";COUNTIF(A2;"*NF521*"))&REPT("+NF522";COUNTIF(A2;"*NF522*"))&REPT("+NF523";COUNTIF(A2;"*NF523*"))&REPT("+AF386";COUNTIF(A2;"*AF386*"));1;1;"")
If in A2 i have words: only fo example: Hdskdsd, jdsdsldd, AF267, jhsffjh, CF707, hfhfhff ffksdfksslAF268. -> Need to see: AF267+CF707+AF268 -> fo example in B2 and to the end.
Thanks in advance.