I have set up a formula to add values to a cell based on other criteria.
However, the cells all have an alpha/numeric value such as M413351100001.

I want to take cell a1 with a value of M513343900001 and increase that
number by a certain value. The formual I have works fine if the cell has
only numeric values. How do I get it to ignore the first character in the
cell (it is always the first character)? In the example above, I want the
new value to be M5133439000006

=IF(N11=1,L11,IF(N111,L11+N11-1))

Thanks-
--
Soni

do you always want to increase +5?
further, what is in L11?


--
Appreciate that you click on the Yes button below if this posting was
helpful.

Thank You

cheers, francis









"soni" wrote:

I have set up a formula to add values to a cell based on other criteria.
However, the cells all have an alpha/numeric value such as M413351100001.

I want to take cell a1 with a value of M513343900001 and increase that
number by a certain value. The formual I have works fine if the cell has
only numeric values. How do I get it to ignore the first character in the
cell (it is always the first character)? In the example above, I want the
new value to be M5133439000006

=IF(N11=1,L11,IF(N111,L11+N11-1))

Thanks-
--
Soni

So, you want to increase it by 5? Try this:

=LEFT(A1,1)&(RIGHT(A1,LEN(A1)-1)+5)

Hope this helps.

Pete

On Jan 8, 4:13*pm, soni wrote:
I have set up a formula to add values to a cell based on other criteria. *
However, the cells all have an alpha/numeric value such as M413351100001.

I want to take cell a1 with a value of M513343900001 and increase that
number by a certain value. *The formual I have works fine if the cell has
only numeric values. *How do I get it to ignore the first character in the
cell (it is always the first character)? *In the example above, I want the
new value to be M5133439000006

=IF(N11=1,L11,IF(N111,L11+N11-1))

Thanks-
--
Soni

hi
your post is confusing.
you want to add a value to the value in A1. but your example formula that
works doens't have anything to do with A1.
where is the other value that you want to add to A1.

to remove the letter substitute A1 for =right(A1,len(A1)-1) in your formula.

post back with clarifications and i will try to help witht he whole formula.
right now, i not entirely sure just what we are trying to add or how much.

Regards
FSt1


"soni" wrote:

I have set up a formula to add values to a cell based on other criteria.
However, the cells all have an alpha/numeric value such as M413351100001.

I want to take cell a1 with a value of M513343900001 and increase that
number by a certain value. The formual I have works fine if the cell has
only numeric values. How do I get it to ignore the first character in the
cell (it is always the first character)? In the example above, I want the
new value to be M5133439000006

=IF(N11=1,L11,IF(N111,L11+N11-1))

Thanks-
--
Soni

Sorry, my two examples were confusing. I want to populate field M11. If
field N11 = 1, then M11 is the same as L11. However, if the value in N11
1, the the value in M11 is the value in L11 incremented by whatever number is
in N11.

Both cell L11 and M11 will have the alpha/numeric data. M5333300001, for
example. I just need to increment the last number in the string by the
amount in the formula. However, because the Alpha char is in the first
position, I'm having trouble making my formula work. So I need to add you
comments to my formula, and copy it to all relevant fields in the table and
it should work?

Thanks for your help
--
Soni


"FSt1" wrote:

hi
your post is confusing.
you want to add a value to the value in A1. but your example formula that
works doens't have anything to do with A1.
where is the other value that you want to add to A1.

to remove the letter substitute A1 for =right(A1,len(A1)-1) in your formula.

post back with clarifications and i will try to help witht he whole formula.
right now, i not entirely sure just what we are trying to add or how much.

Regards
FSt1


"soni" wrote:

I have set up a formula to add values to a cell based on other criteria.
However, the cells all have an alpha/numeric value such as M413351100001.

I want to take cell a1 with a value of M513343900001 and increase that
number by a certain value. The formual I have works fine if the cell has
only numeric values. How do I get it to ignore the first character in the
cell (it is always the first character)? In the example above, I want the
new value to be M5133439000006

=IF(N11=1,L11,IF(N111,L11+N11-1))

Thanks-
--
Soni

Okay, put this formula in M11:

=IF(N11=1,L11,IF(N111,LEFT(L11,1)&(RIGHT(L11,LEN( L11)-1)+N11-1)))

You seem to be adding N11-1 (judging by your earlier formula), so you
might be able to use just this:

=LEFT(L11,1)&(RIGHT(L11,LEN(L11)-1)+N11-1)

assuming N11 is always 1 or more.

Hope this helps.

Pete

On Jan 8, 8:25*pm, soni wrote:
Sorry, my two examples were confusing. *I want to populate field M11. *If
field N11 = 1, then M11 is the same as L11. *However, if the value in N11
1, the the value in M11 is the value in L11 incremented by whatever number is
in N11.

Both cell L11 and M11 will have the alpha/numeric data. *M5333300001, for
example. *I just need to increment the last number in the string by the
amount in the formula. *However, because the Alpha char is in the first
position, I'm having trouble making my formula work. *So I need to add you
comments to my formula, and copy it to all relevant fields in the table and
it should work?

Thanks for your help
--
Soni



"FSt1" wrote:
hi
your post is confusing.
you want to add a value to the value in A1. but your example formula that
works doens't have anything to do with A1.
where is the other value that you want to add to A1.

to remove the letter substitute A1 for =right(A1,len(A1)-1) in your formula.

post back with clarifications and i will try to help witht he whole formula.
right now, i not entirely sure just what we are trying to add or how much.

Regards
FSt1

"soni" wrote:

I have set up a formula to add values to a cell based on other criteria. *
However, the cells all have an alpha/numeric value such as M413351100001.

I want to take cell a1 with a value of M513343900001 and increase that
number by a certain value. *The formual I have works fine if the cell has
only numeric values. *How do I get it to ignore the first character in the
cell (it is always the first character)? *In the example above, I want the
new value to be M5133439000006

=IF(N11=1,L11,IF(N111,L11+N11-1))

Thanks-
--
Soni- Hide quoted text -

- Show quoted text -

Hi Pete-
Thanks for the response. I tried them both out of curiosity, and they both
work beautifully.

Thank you so much for your help!!!
--
Soni


"Pete_UK" wrote:

Okay, put this formula in M11:

=IF(N11=1,L11,IF(N111,LEFT(L11,1)&(RIGHT(L11,LEN( L11)-1)+N11-1)))

You seem to be adding N11-1 (judging by your earlier formula), so you
might be able to use just this:

=LEFT(L11,1)&(RIGHT(L11,LEN(L11)-1)+N11-1)

assuming N11 is always 1 or more.

Hope this helps.

Pete

On Jan 8, 8:25 pm, soni wrote:
Sorry, my two examples were confusing. I want to populate field M11. If
field N11 = 1, then M11 is the same as L11. However, if the value in N11
1, the the value in M11 is the value in L11 incremented by whatever number is
in N11.

Both cell L11 and M11 will have the alpha/numeric data. M5333300001, for
example. I just need to increment the last number in the string by the
amount in the formula. However, because the Alpha char is in the first
position, I'm having trouble making my formula work. So I need to add you
comments to my formula, and copy it to all relevant fields in the table and
it should work?

Thanks for your help
--
Soni



"FSt1" wrote:
hi
your post is confusing.
you want to add a value to the value in A1. but your example formula that
works doens't have anything to do with A1.
where is the other value that you want to add to A1.

to remove the letter substitute A1 for =right(A1,len(A1)-1) in your formula.

post back with clarifications and i will try to help witht he whole formula.
right now, i not entirely sure just what we are trying to add or how much.

Regards
FSt1

"soni" wrote:

I have set up a formula to add values to a cell based on other criteria.
However, the cells all have an alpha/numeric value such as M413351100001.

I want to take cell a1 with a value of M513343900001 and increase that
number by a certain value. The formual I have works fine if the cell has
only numeric values. How do I get it to ignore the first character in the
cell (it is always the first character)? In the example above, I want the
new value to be M5133439000006

=IF(N11=1,L11,IF(N111,L11+N11-1))

Thanks-
--
Soni- Hide quoted text -

- Show quoted text -

You're welcome, Soni - thanks for feeding back.

Pete

On Jan 9, 2:52*pm, soni wrote:
Hi Pete-
Thanks for the response. *I tried them both out of curiosity, and they both
work beautifully. *

Thank you so much for your help!!!
--
Soni



"Pete_UK" wrote:
Okay, put this formula in M11:

=IF(N11=1,L11,IF(N111,LEFT(L11,1)&(RIGHT(L11,LEN( L11)-1)+N11-1)))

You seem to be adding N11-1 (judging by your earlier formula), so you
might be able to use just this:

=LEFT(L11,1)&(RIGHT(L11,LEN(L11)-1)+N11-1)

assuming N11 is always 1 or more.

Hope this helps.

Pete

On Jan 8, 8:25 pm, soni wrote:
Sorry, my two examples were confusing. *I want to populate field M11. *If
field N11 = 1, then M11 is the same as L11. *However, if the value in N11
1, the the value in M11 is the value in L11 incremented by whatever number is
in N11.

Both cell L11 and M11 will have the alpha/numeric data. *M5333300001, for
example. *I just need to increment the last number in the string by the
amount in the formula. *However, because the Alpha char is in the first
position, I'm having trouble making my formula work. *So I need to add you
comments to my formula, and copy it to all relevant fields in the table and
it should work?

Thanks for your help
--
Soni

"FSt1" wrote:
hi
your post is confusing.
you want to add a value to the value in A1. but your example formula that
works doens't have anything to do with A1.
where is the other value that you want to add to A1.

to remove the letter substitute A1 for =right(A1,len(A1)-1) in your formula.

post back with clarifications and i will try to help witht he whole formula.
right now, i not entirely sure just what we are trying to add or how much.

Regards
FSt1

"soni" wrote:

I have set up a formula to add values to a cell based on other criteria. *
However, the cells all have an alpha/numeric value such as M413351100001.

I want to take cell a1 with a value of M513343900001 and increase that
number by a certain value. *The formual I have works fine if the cell has
only numeric values. *How do I get it to ignore the first character in the
cell (it is always the first character)? *In the example above, I want the
new value to be M5133439000006

=IF(N11=1,L11,IF(N111,L11+N11-1))

Thanks-
--
Soni- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -