Return a digit in a string of numbers

[W M]

Windows XP
Office XP

I would like to know if there is a formula which will return a specified digit in a string of digits. For example, assuming I have a number 5891034 in cell A1, then I want to place a formulas into cell A2-A8 that will separate each digit from that number into cells so that cell A2 would return the first digit "5", cell A3 returns the second digit "8", cell A4 returns the third digit "9", cell A5 returns the fourth digit "1", etc.

Is this possible?

Thanks in advance for any help you can provide.

 

[Trevor Shuttleworth]

You could just use Data | text to columns ... | Fixed length (1 character)

Alternatively you could use MID to extract characters

=MID(A1,1,1), =MID(A1,2,1), etc

Regards

Trevor


Windows XP
Office XP

I would like to know if there is a formula which will return a specified digit in a string of digits. For example, assuming I have a number 5891034 in cell A1, then I want to place a formulas into cell A2-A8 that will separate each digit from that number into cells so that cell A2 would return the first digit "5", cell A3 returns the second digit "8", cell A4 returns the third digit "9", cell A5 returns the fourth digit "1", etc.

Is this possible?

Thanks in advance for any help you can provide.

 

[excelmunkey]

Hi WM,

If your value 5891034 is in cell A1 use this formula:
=MID($A$1,ROW()-1,1)
Should the value be in an alternative cell modify the formula
accordingly, remembering that it requires an absolute cell reference
($s).

Cheers,
JF

 

[W M]

May you be richly blessed by the deity of your choice!
Hi WM,

If your value 5891034 is in cell A1 use this formula:
=MID($A$1,ROW()-1,1)
Should the value be in an alternative cell modify the formula
accordingly, remembering that it requires an absolute cell reference
($s).

Cheers,
JF

 

[W M]

Thank you, I ended up using the Mid formula. I knew I had seen it somewhere in the formula listing before, but as you know, the older you get, the fewer powers of recall you have, it seems. Thank you so much for taking time to respond.
You could just use Data | text to columns ... | Fixed length (1 character)

Alternatively you could use MID to extract characters

=MID(A1,1,1), =MID(A1,2,1), etc

Regards

Trevor


Windows XP
Office XP

I would like to know if there is a formula which will return a specified digit in a string of digits. For example, assuming I have a number 5891034 in cell A1, then I want to place a formulas into cell A2-A8 that will separate each digit from that number into cells so that cell A2 would return the first digit "5", cell A3 returns the second digit "8", cell A4 returns the third digit "9", cell A5 returns the fourth digit "1", etc.

Is this possible?

Thanks in advance for any help you can provide.

 

[Trevor Shuttleworth]

Thanks for the feedback. Glad you got a helpful response or two. Must admit I'd not taken into account that you were putting the data in rows rather than columns. Another sign of old age ! ExcelMunkey's suggestion is less labour intensive as you only need to enter the formula once and the drag down ... also more scaleable should the number be longer.

Regards

Trevor

Thank you, I ended up using the Mid formula. I knew I had seen it somewhere in the formula listing before, but as you know, the older you get, the fewer powers of recall you have, it seems. Thank you so much for taking time to respond.
You could just use Data | text to columns ... | Fixed length (1 character)

Alternatively you could use MID to extract characters

=MID(A1,1,1), =MID(A1,2,1), etc

Regards

Trevor


Windows XP
Office XP

I would like to know if there is a formula which will return a specified digit in a string of digits. For example, assuming I have a number 5891034 in cell A1, then I want to place a formulas into cell A2-A8 that will separate each digit from that number into cells so that cell A2 would return the first digit "5", cell A3 returns the second digit "8", cell A4 returns the third digit "9", cell A5 returns the fourth digit "1", etc.

Is this possible?

Thanks in advance for any help you can provide.