Using IF functions to merge multiple formulas

orglenda1740 You can use the Formula SUBSTITUTE to replace instances of a character or a particular text string within a longer string.

I am using this sometimes up to 11 times so far when creating unique references between databases in different formats (same data but recorded by different departments). You wrap the SUBSTITUTE formula around the previous one, for additional text correction. I think there is no limit to number of times you can use. TRIM formula is useful to get rid of extra spaces at the end of a cell total string, and if double spaced within the string.

Regarding identifying Regular Expressions I am using a macro added to a macro enabled workbook. This works well and you should find examples online, I am using one from ExcelDemy, but I seem to be unable to get the macro to work in multiple xlsm files. You can wrap other Excel formulas around the RegEx.

I use SUBSTITUTE with TEXTBEFORE and TEXTAFTER, the latter two are interesting, as they can be nested via the last optional value in the formula if a word is not found. This was critical for me when extracting a unique reference from address data in my case recreating the High Rise Block names, that end with 3 unique words. When trapping the word before these 3 words via a nested TEXTBEFORE, in a 2nd helper column you use TEXTAFTER to trap the last word, and then add them together via contacting with & in a third column.

orglenda1740 
1. Revised formula

=IF(ISNUMBER(LEFT(B1,1)*1), LEFT(B1, LEN(B1)-1), RIGHT(B1, LEN(B1)-1))

2. Combined formula:

=IFERROR(IF(LEFT($F10,3)="986", RIGHT($F10,3), RIGHT($F10,3)*LEFT($F10,3)), "")