I have cell values with names that are fairly close to each other. I would like to extract a common sub-string.
Here is an example.
1100_250_Jump_12HR_100MD_S_run1
1100_250_Jump_12HR_100MD_S_run2
1100_250_Jump_12HR_100MD_S_run3
1100_250_Jump_12HR_100MD_S_run4
1101_250_Jump_12HR_100MD_U_run5
1101_250_Jump_12HR_100MD_U_run6
1102_250_Jump_12HR_100MD_U_run7
1102_250_Jump_12HR_100MD_U_run8
1102_250_Jump_12HR_100MD_U_run9
1102_250_Jump_12HR_100MD_U_run10
The output should be _250_Jump_12HR_100MD_
How can I extract a common sub-string in Excel?
I checked similar questions but could not find the answer.
Best Answer
Based on the UDF in the link I posted in comments:
Then, assuming you strings are in column
A
, Use the UDF inB1
like this:Then in
B2
:And populate down.
Cells in column
B
in any given row now show the common string for all cells inA
up to that row.