creating a formula for sequence of numbers

LEIAMWALSH1

New Member
Joined
Dec 15, 2009
Messages
4
Hi All,

I would really appreciate some help in creating a formula for a number sequence. It starts with this number....

765-88346941

Then you add 11 to get......

765-88346952

this goes on adding 11 each time until the last digit is a 6. When the last digit is a 6 you just add 4, and then back to adding 11.

I hope someone will understand me!! Any input would be appreciated greatly
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Welcome to the board...

try

=LEFT(A1,LEN(A1)-2)&RIGHT(A1,2)+IF(RIGHT(A1)="6",4,11)
 

LEIAMWALSH1

New Member
Joined
Dec 15, 2009
Messages
4
hey jonmo,

thanks for the quick reply! Microsoft is saying there is an error in the formula? to be honest im not to quick with excel....any more help would be appriciated
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,881
Hi LEIAMWALSH1
Welcome to the board

jonmo: I believe you meant the last 8 digits instead of the last 2:

=LEFT(A1,LEN(A1)-8)&RIGHT(A1,8)+IF(RIGHT(A1)="6",4,11)
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,881

ADVERTISEMENT

LEIAMWALSH1

I just tested it with no problem.

Write 765-88346941 in A1, the formula in A2 and drag down.
 

Nubian88

New Member
Joined
Dec 15, 2009
Messages
2
I am also having the same problem at work. I need help withthe same thing!

I have looked at the formula but I don't when where to start... Where do i write the number sequence and then how do i apply the formula? Im new to all this stuff
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061

ADVERTISEMENT

Hi LEIAMWALSH1
Welcome to the board

jonmo: I believe you meant the last 8 digits instead of the last 2:

=LEFT(A1,LEN(A1)-8)&RIGHT(A1,8)+IF(RIGHT(A1)="6",4,11)

No, I actually meant 2. But my logic was flawed...LOL..

Anyway, 8 may not be good enough either. EVENTUALLY that 8 digit number will turn into a 9 digit number, then 8 won't be good enough anymore...

Perhaps it needs to use the find("-"...)

=LEFT(A1,FIND("-",A1))&RIGHT(A1,(LEN(A1)-FIND("-",A1)))+IF(RIGHT(A1)="6",4,11)
 
Last edited:

LEIAMWALSH1

New Member
Joined
Dec 15, 2009
Messages
4
Ok, It does seem to work with next number in sequence.....but i want to continue the sequence on and on...any further input?
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,881
Just copy or drag the formula down.

Also read jonmo's last post, if the code can have more than 8 digits after the hyphen better use his last formula (good point, jonmo).
 

Watch MrExcel Video

Forum statistics

Threads
1,122,517
Messages
5,596,619
Members
414,081
Latest member
Subaru_Steve

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top