To be got Alphabetic

Moideen wrote:

> We Need Always 3 Digits.This function only comming 2 Digits, Pls Advice
> me on this matter.
>
> Eg: 1.550 , Shown only : A.FF, To be Shown : A.FFS
>
> =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBST ITUTE(SUBSTITUTE
> (A1,1,"A"),2,"B"),3,"C"),4,"D"),5,"F"),0,"S")

Works for me. Shrug. Try switching to the VBA solution:

Private Sub Worksheet_Change(ByVal Target As Range)
For Each cell In Target
If cell.Column = 1 Then cell.Offset(0, 1).Value = Replace(Replace( _
Replace(Replace(Replace(Replace(cell.Value, 1, "A"), 2, "B"), _
3, "C"), 4, "D"), 5, "F"), 0, "S")
Next
End Sub

--
Where is your savior now?

Originally Posted by Auric__

Moideen wrote:

> We Need Always 3 Digits.This function only comming 2 Digits, Pls Advice
> me on this matter.
>
> Eg: 1.550 , Shown only : A.FF, To be Shown : A.FFS
>
> =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBST ITUTE(SUBSTITUTE
> (A1,1,"A"),2,"B"),3,"C"),4,"D"),5,"F"),0,"S")

Works for me. Shrug. Try switching to the VBA solution:

Private Sub Worksheet_Change(ByVal Target As Range)
For Each cell In Target
If cell.Column = 1 Then cell.Offset(0, 1).Value = Replace(Replace( _
Replace(Replace(Replace(Replace(cell.Value, 1, "A"), 2, "B"), _
3, "C"), 4, "D"), 5, "F"), 0, "S")
Next
End Sub

--
Where is your savior now?

Not Getting..

Eg: 1.550 , Shown only : A.FF, To be Shown : A.FFS

[quote=Auric__;1603366]Moideen wrote:

> We Need Always 3 Digits.This function only comming 2 Digits, Pls Advice
> me on this matter.
>
> Eg: 1.550 , Shown only : A.FF, To be Shown : A.FFS
>
> =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBST ITUTE(SUBSTITUTE
> (A1,1,"A"),2,"B"),3,"C"),4,"D"),5,"F"),0,"S")

Works for me. Shrug. Try switching to the VBA solution:

Private Sub Worksheet_Change(ByVal Target As Range)
For Each cell In Target
If cell.Column = 1 Then cell.Offset(0, 1).Value = Replace(Replace( _
Replace(Replace(Replace(Replace(cell.Value, 1, "A"), 2, "B"), _
3, "C"), 4, "D"), 5, "F"), 0, "S")
Next
End Sub

--
I Tried with Above VBA code, Not Getting
Eg: 1.550 , Shown only : A.FF, To be Shown : A.FFS

Moideen wrote:

> Auric__;1603366 Wrote:
>> Moideen wrote:
>> -
>> > We Need Always 3 Digits.This function only comming 2 Digits, Pls
>> Advice
>> > me on this matter.
>> >
>> > Eg: 1.550 , Shown only : A.FF, To be Shown : A.FFS
>> >
>> > =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBST ITUTE(SUBSTITUTE
>> > (A1,1,"A"),2,"B"),3,"C"),4,"D"),5,"F"),0,"S")-
>>
>> Works for me. Shrug. Try switching to the VBA solution:
>>
>> Private Sub Worksheet_Change(ByVal Target As Range)
>> For Each cell In Target
>> If cell.Column = 1 Then cell.Offset(0, 1).Value = Replace(Replace(
>> _
>> Replace(Replace(Replace(Replace(cell.Value, 1, "A"), 2, "B"),
>> _
>> 3, "C"), 4, "D"), 5, "F"), 0, "S")
>> Next
>> End Sub
>
> Not Getting..
>
> Eg: 1.550 , Shown only : A.FF, To be Shown : A.FFS

Then there's some difference between your system and mine, possibly something
wrong. Everything that I've posted works as expected for me.

--
SPIDERS! One had a name, even.

Moideen wrote:

> Not Getting..
>
> Eg: 1.550 , Shown only : A.FF, To be Shown : A.FFS

Okay, I've found the problem (which was in part due to me misunderstanding
your problem). Use one of these:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBST ITUTE(SUBSTITUTE(FIXED
(A1,3,1),1,"A"),2,"B"),3,"C"),4,"D"),5,"F"),0,"S")

....or...

Private Sub Worksheet_Change(ByVal Target As Range)
For Each cell In Target
If cell.Column = 1 Then cell.Offset(0, 1).Value = Replace(Replace( _
Replace(Replace(Replace(Replace(Format(cell.Value, "#.000"), _
1, "A"), 2, "B"), 3, "C"), 4, "D"), 5, "F"), 0, "S")
Next
End Sub

Note that both of these will *always* give you 3 decimal places,
regardless of the source data:

1 A.SSS
1.55 A.FFS
2122.33333 BABB.CCC

Also, both will remove any commas (thousands separators) from the number.

--
She had accepted it once but now rejected it and
could not give all of her reasons for this change.

Originally Posted by Auric__

Moideen wrote:

> Not Getting..
>
> Eg: 1.550 , Shown only : A.FF, To be Shown : A.FFS

Okay, I've found the problem (which was in part due to me misunderstanding
your problem). Use one of these:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBST ITUTE(SUBSTITUTE(FIXED
(A1,3,1),1,"A"),2,"B"),3,"C"),4,"D"),5,"F"),0,"S")

....or...

Private Sub Worksheet_Change(ByVal Target As Range)
For Each cell In Target
If cell.Column = 1 Then cell.Offset(0, 1).Value = Replace(Replace( _
Replace(Replace(Replace(Replace(Format(cell.Value, "#.000"), _
1, "A"), 2, "B"), 3, "C"), 4, "D"), 5, "F"), 0, "S")
Next
End Sub

Note that both of these will *always* give you 3 decimal places,
regardless of the source data:

1 A.SSS
1.55 A.FFS
2122.33333 BABB.CCC

Also, both will remove any commas (thousands separators) from the number.

--
She had accepted it once but now rejected it and
could not give all of her reasons for this change.

Now Okay, Thank you very much.