IF statement based on number count on excel

Question

How do I properly create a syntax for IF-ELSE statement that will insert a text?

Scenario:

The goal is to insert a text in column H based on the text from column B

First rule: If column B starts with either SB or HB, write a text like "The 2020 amendment to this section by S.B. 123"

The S.B. should become H.B. if column B starts with HB

So I did:

=IF(LEFT(B2,2)="HB","The 2020 amendment to this section by H.B. "&(RIGHT(B2,4)),"The 2020 amendment to this section by S.B. ")

The above statement is not enough. I need to insert more IFs for the number of digits after the HB/SB

So I need something if it has 4 digits, 3 digits, and 2 digits.

I tried making the first IF nested inside IF but failed. I did something like this:

=IF(LEN(B2="8" and so on, insert first IF meaning if B2 has 8 character including the spaces

I also tried:

IF(B2=8) and so on, insert first IF

I keep trying but I keep getting the error in my formula. I will really appreciate any help.

Answer 1

One of these two formulas will work for you. Depends on how column B is populated and how to populate column H. Pick from one of these two:

If column B only starts with SB or HB, then use formula 1 below.

If column B has more than just SB or HB as the initials but you only want to write the language column if B starts with SB or HB, then use formula 2 below. Any other prefixes will be ignored.

Formula 1 in column H:

    =IF(LEFT(B2,2)="SB","The 2020 amendment to this section by S.B. "&RIGHT(B2,LEN(B2)-FIND(" ",B2)),"The 2020 amendment to this section by H.B. "&RIGHT(B2,LEN(B2)-FIND(" ",B2)))

Formula 2 in column H:

    =IF(OR(LEFT(B5,2)="SB",LEFT(B5,2)="HB"),IF(LEFT(B5,2)="SB","The 2020 amendment to this section by S.B. "&RIGHT(B5,LEN(B5)-FIND(" ",B5)),"The 2020 amendment to this section by H.B. "&RIGHT(B5,LEN(B5)-FIND(" ",B5))),"")