Excel If function I think

Closed
Kris - Dec 30, 2009 at 12:47 PM
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Dec 30, 2009 at 11:40 PM
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

venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Dec 30, 2009 at 11:40 PM
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