View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.misc
Tevuna Tevuna is offline
external usenet poster
 
Posts: 136
Default Excel formula needed... regular expression?

a) Place the text strings in column A, starting at row 2
b) Enter number 97-122 in columns B-AA in row 1. In cell AB1, enter 32
c) Paste this formula in cell B2:
=SUBSTITUTE(A2,CHAR(B$1),"")
d) copy across row 2, from B2-AB2
e) copy down all columns
f) Copy AB:AB, Paste Paste special Values
g) Delete columns B-AA

"danesh" wrote:

A1 contains "TC_1 and TC_2 are really cool TC_5 and TC_6, but TC_7 is better"

B1 should use a formula to generate "TC_1, TC_2, TC_5, TC_6, TC_7"

There are 550 rows, so the formula should be flexible enough to apply to "N"
number of matches, and insert the matches into column B corresponding to the
entry in column A of the same row.

A2 contains "TC_1 and TC_22 are really cool TC_5"
B2 should use a formula to generate "TC_1, TC_22, TC_5"