Formula to insert special character in a string

[saravanan2021]

Hi,
I have string in Column B.

I need vba to find first alphabet (a-z) and insert character "^".

Please refer attachment, in b2 I have a string in C2 first alphabet is f. Hence "^" symbol is inserted before f.

[WideBoyDixon]

VBA solution:

Please Login or Register  to view this content.

WBD

[nimrod1313]

METHOD A

Please Login or Register  to view this content.

METHOD B

Please Login or Register  to view this content.

Assumption: Works if the last digits before the letters is a 4 digit year ... as is in all your examples

[nimrod1313]

updated code from last post so that the "intent" is clearer

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[nimrod1313]

SOLUTION IF YEAR ASSUMPTION IS INVALID
(i.e. traversing the string looking for the change from numbers to letters.)

Please Login or Register  to view this content.