Excel If function I think

[Closed]
Report
-
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
-
Hello,

I have a set of data in Column A where the policy numbers comes in three formats

1) 7000055555-03
2) 012222222-00
3) 001111111-00

After running an extractnumber function my output in Column B is this:

1) 700005555503
2) 1222222200
3) 111111100

What I want is in Column C to reference if the data starts with a 7 in column A, then to pull the left 10 digits in column B, if the data in column A starts with 01, then to pull the left 8 digits from column B, and then if the data in column A starts with 00 to pull the left 7 digits in column B.

Any help would be appreciated!

1 reply

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
803
try this formula in C1 and copy C1 down

=IF(LEFT(A1,1)="7",LEFT(B1,10),IF(LEFT(A1,2)="01",LEFT(B1,8),IF(LEFT(A1,2)="00",LEFT(B1,7),"")))
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month