Yes I have a lot of text that contain a lot of this type 1a 3c 2ffind 1a and replace with 1A - or is it more complicated ?
For Each rrow In rowRange
RowValue = rrow.Row
Range("D" & RowValue).Select
ActiveCell.FormulaR1C1 = "=MID(R[0]C[-1],SEARCH(""Band"",R[0]C[-1])+5,1)"
Next rrow
{=MID([COLOR=blue]$A1,MIN([COLOR=red]SEARCH([COLOR=green]{0,1,2,3,4,5,6,7,8,9},$A1&"0123456789"[/COLOR])[/COLOR]),MAX([COLOR=red]IF([COLOR=green]ISNUMBER([COLOR=purple]VALUE([COLOR=teal]MID([COLOR=#ff00ff]$A1,ROW([COLOR=navy]INDIRECT([COLOR=blue]"1:" & LEN([COLOR=red]$A1[/COLOR])[/COLOR])[/COLOR]),1[/COLOR])[/COLOR])[/COLOR]),ROW([COLOR=purple]INDIRECT([COLOR=teal]"1:" & LEN([COLOR=#ff00ff]$A1[/COLOR])[/COLOR])[/COLOR])[/COLOR])[/COLOR])-MIN([COLOR=red]SEARCH([COLOR=green]{0,1,2,3,4,5,6,7,8,9},$A1&"0123456789"[/COLOR])[/COLOR])+1[/COLOR])}
"Comp 5c Train 2 Stage 1 Flocculator No.3 Simocode |
Comp 6a Train 2 Stage 2 Flocculator No.1 Variable Speed Drive |
Comp 6a Train 2 Stage 2 Flocculator No.1 Simocode |
Comp 6b Train 2 Stage 2 Flocculator No.2 Variable Speed Drive |
Comp 6b Train 2 Stage 2 Flocculator No.2 Simocode |
Comp 6c Train 2 Stage 2 Flocculator No.3 Variable Speed Drive |
Comp 6c Train 2 Stage 2 Flocculator No.3 Simocode" |