Previous Topic Next Topic

Excel If function I think

Closed
Kris - 30 Dec 2009 à 12:47
venkat1926 Posts 1863 Registration date Sunday 14 June 2009 Status Contributor Last seen 7 August 2021 - 30 Dec 2009 à 23:40
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!
Related:

1 response

Réponse 1 / 1
venkat1926 Posts 1863 Registration date Sunday 14 June 2009 Status Contributor Last seen 7 August 2021 811
30 Dec 2009 à 23:40
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),"")))