I have a rather complicated scenario that I need to resolve and I am hoping
that someone can guide me.

I need to take a range of numbers in a column (could be positive or negative
numbers) and convert the last digit of only the negative numbers in the
column. The last digit will be converted to a character based on a series of
values associated with the last digit. The values are as follows: p = 0, q =
-1, r = -2, s= -3, t = -4, u = -5, v = -6, w = -7, x = -8, and y = -9.

For example, the number -11523.64 would actually need to read 11523.6t
(because it's a negative number, the last digit is replaced with a "t" which
is -4's value). Conversly, if the number were 11523.64, then the number would
remain exactly as it appears as a positive 11523.64.

Any assistance would be greatly appreciated.

--
J Harris

What if the number is -100.45000000001?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"J Harris" wrote in message ...
|I have a rather complicated scenario that I need to resolve and I am hoping
| that someone can guide me.
|
| I need to take a range of numbers in a column (could be positive or negative
| numbers) and convert the last digit of only the negative numbers in the
| column. The last digit will be converted to a character based on a series of
| values associated with the last digit. The values are as follows: p = 0, q =
| -1, r = -2, s= -3, t = -4, u = -5, v = -6, w = -7, x = -8, and y = -9.
|
| For example, the number -11523.64 would actually need to read 11523.6t
| (because it's a negative number, the last digit is replaced with a "t" which
| is -4's value). Conversly, if the number were 11523.64, then the number would
| remain exactly as it appears as a positive 11523.64.
|
| Any assistance would be greatly appreciated.
|
| --
| J Harris

With your numbers in column A starting in A1, enter this formula in B1:

=IF(ISBLANK(A1),"",IF(A10,""&A1,MID(A1,2,LEN(A1)-2)&CHAR(112+RIGHT(A1,1))))

then copy down. If there is no number in A, this will return a blank,
otherwise it will do the transformation you require and the output will
be text. If you require positive numbers to still be numbers, then
remove the ""& in the second IF clause.

Do you mind telling me why you want to do this?

Hope this helps.

Pete

J Harris wrote:
I have a rather complicated scenario that I need to resolve and I am hoping
that someone can guide me.

I need to take a range of numbers in a column (could be positive or negative
numbers) and convert the last digit of only the negative numbers in the
column. The last digit will be converted to a character based on a series of
values associated with the last digit. The values are as follows: p = 0, q =
-1, r = -2, s= -3, t = -4, u = -5, v = -6, w = -7, x = -8, and y = -9.

For example, the number -11523.64 would actually need to read 11523.6t
(because it's a negative number, the last digit is replaced with a "t" which
is -4's value). Conversly, if the number were 11523.64, then the number would
remain exactly as it appears as a positive 11523.64.

Any assistance would be greatly appreciated.

--
J Harris

This is what my formula produces, Niek, with -100.4500001:

100.450000q

Pete

Niek Otten wrote:
What if the number is -100.45000000001?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"J Harris" wrote in message ...
|I have a rather complicated scenario that I need to resolve and I am hoping
| that someone can guide me.
|
| I need to take a range of numbers in a column (could be positive or negative
| numbers) and convert the last digit of only the negative numbers in the
| column. The last digit will be converted to a character based on a series of
| values associated with the last digit. The values are as follows: p = 0, q =
| -1, r = -2, s= -3, t = -4, u = -5, v = -6, w = -7, x = -8, and y = -9.
|
| For example, the number -11523.64 would actually need to read 11523.6t
| (because it's a negative number, the last digit is replaced with a "t" which
| is -4's value). Conversly, if the number were 11523.64, then the number would
| remain exactly as it appears as a positive 11523.64.
|
| Any assistance would be greatly appreciated.
|
| --
| J Harris

What if the number is -10.10 ?

The 0 will only be displayed if the cell is formatted but the true
underlying value is really -10.1.

Biff

"Niek Otten" wrote in message
...
What if the number is -100.45000000001?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"J Harris" wrote in message
...
|I have a rather complicated scenario that I need to resolve and I am
hoping
| that someone can guide me.
|
| I need to take a range of numbers in a column (could be positive or
negative
| numbers) and convert the last digit of only the negative numbers in the
| column. The last digit will be converted to a character based on a
series of
| values associated with the last digit. The values are as follows: p = 0,
q =
| -1, r = -2, s= -3, t = -4, u = -5, v = -6, w = -7, x = -8, and y = -9.
|
| For example, the number -11523.64 would actually need to read 11523.6t
| (because it's a negative number, the last digit is replaced with a "t"
which
| is -4's value). Conversly, if the number were 11523.64, then the number
would
| remain exactly as it appears as a positive 11523.64.
|
| Any assistance would be greatly appreciated.
|
| --
| J Harris

Sorry, I've just tested it with zero, and it needs a slight amendment -
the second IF should be:

IF(A1=0, etc, instead of IF(A10,

Hope this helps (more thoroughly).

Pete

Pete_UK wrote:
With your numbers in column A starting in A1, enter this formula in B1:

=IF(ISBLANK(A1),"",IF(A10,""&A1,MID(A1,2,LEN(A1)-2)&CHAR(112+RIGHT(A1,1))))

then copy down. If there is no number in A, this will return a blank,
otherwise it will do the transformation you require and the output will
be text. If you require positive numbers to still be numbers, then
remove the ""& in the second IF clause.

Do you mind telling me why you want to do this?

Hope this helps.

Pete

J Harris wrote:
I have a rather complicated scenario that I need to resolve and I am hoping
that someone can guide me.

I need to take a range of numbers in a column (could be positive or negative
numbers) and convert the last digit of only the negative numbers in the
column. The last digit will be converted to a character based on a series of
values associated with the last digit. The values are as follows: p = 0, q =
-1, r = -2, s= -3, t = -4, u = -5, v = -6, w = -7, x = -8, and y = -9.

For example, the number -11523.64 would actually need to read 11523.6t
(because it's a negative number, the last digit is replaced with a "t" which
is -4's value). Conversly, if the number were 11523.64, then the number would
remain exactly as it appears as a positive 11523.64.

Any assistance would be greatly appreciated.

--
J Harris

Biff,

my formula produces 10.q

Pete

Biff wrote:
What if the number is -10.10 ?

The 0 will only be displayed if the cell is formatted but the true
underlying value is really -10.1.

Biff

"Niek Otten" wrote in message
...
What if the number is -100.45000000001?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"J Harris" wrote in message
...
|I have a rather complicated scenario that I need to resolve and I am
hoping
| that someone can guide me.
|
| I need to take a range of numbers in a column (could be positive or
negative
| numbers) and convert the last digit of only the negative numbers in the
| column. The last digit will be converted to a character based on a
series of
| values associated with the last digit. The values are as follows: p = 0,
q =
| -1, r = -2, s= -3, t = -4, u = -5, v = -6, w = -7, x = -8, and y = -9.
|
| For example, the number -11523.64 would actually need to read 11523.6t
| (because it's a negative number, the last digit is replaced with a "t"
which
| is -4's value). Conversly, if the number were 11523.64, then the number
would
| remain exactly as it appears as a positive 11523.64.
|
| Any assistance would be greatly appreciated.
|
| --
| J Harris

Some other strange-looking, though logical, results:

-9 produces y
-15 produces 1u,
-200 produces 20p
-1600 produces 160p.

Pete

Pete_UK wrote:
Sorry, I've just tested it with zero, and it needs a slight amendment -
the second IF should be:

IF(A1=0, etc, instead of IF(A10,

Hope this helps (more thoroughly).

Pete

Pete_UK wrote:
With your numbers in column A starting in A1, enter this formula in B1:

=IF(ISBLANK(A1),"",IF(A10,""&A1,MID(A1,2,LEN(A1)-2)&CHAR(112+RIGHT(A1,1))))

then copy down. If there is no number in A, this will return a blank,
otherwise it will do the transformation you require and the output will
be text. If you require positive numbers to still be numbers, then
remove the ""& in the second IF clause.

Do you mind telling me why you want to do this?

Hope this helps.

Pete

J Harris wrote:
I have a rather complicated scenario that I need to resolve and I am hoping
that someone can guide me.

I need to take a range of numbers in a column (could be positive or negative
numbers) and convert the last digit of only the negative numbers in the
column. The last digit will be converted to a character based on a series of
values associated with the last digit. The values are as follows: p = 0, q =
-1, r = -2, s= -3, t = -4, u = -5, v = -6, w = -7, x = -8, and y = -9.

For example, the number -11523.64 would actually need to read 11523.6t
(because it's a negative number, the last digit is replaced with a "t" which
is -4's value). Conversly, if the number were 11523.64, then the number would
remain exactly as it appears as a positive 11523.64.

Any assistance would be greatly appreciated.

--
J Harris