ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   To be got Alphabetic (https://www.excelbanter.com/excel-programming/446456-got-alphabetic.html)

Moideen
To be got Alphabetic
 
We are Maintaining cost in English Letters, Kindly Help to get COST on Coloumn2

Examples,

A : 1
B : 2
C : 3
D : 4
F : 5

If I Entered 242 on Coloumn1 Need Automatic Shown BDB on Coloumn2

Vacuum Sealed
To be got Alphabetic
 
On 29/06/2012 10:53 PM, Moideen wrote:
We are Maintaining cost in English Letters, Kindly Help to get COST on
Coloumn2

Examples,

A : 1
B : 2
C : 3
D : 4
F : 5

If I Entered 242 on Coloumn1 Need Automatic Shown BDB on Coloumn2




Well

I don't quiet follow what you mean by COST on Column 2 as your end
statement, you are asking for "BDB" to be shown if 242 is entered into
the adjacent cell in Column 1

FWIW:

if A2 is the active cell, then in B2 this:

=IF($A2=242,"BDB","")

Copy down as required..

HTH
Mick.

Auric__
To be got Alphabetic
 
Moideen wrote:

We are Maintaining cost in English Letters, Kindly Help to get COST on
Coloumn2

Examples,

A : 1
B : 2
C : 3
D : 4
F : 5

If I Entered 242 on Coloumn1 Need Automatic Shown BDB on Coloumn2

Put this in the sheet's object in the VBA editor:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range, outP As String, costs As Variant
costs = Array("#", "A", "B", "C", "D", "F", "#", "#", "#", "#")
For Each cell In Target
If cell.Column = 1 Then
outP = ""
c = Abs(Val(cell.Value))
Do While c 0
n = c Mod 10
c = c \ 10
outP = costs(n) & outP
Loop
Me.Cells(cell.Row, cell.Column + 1).Value = outP
End If
Next
End Sub

Edit the 'costs' array to fit. (The hashes are there to indicate data entry
errors. If you don't want them, don't delete them -- change them to "".)

If this is meant to apply to the entire workbook, put this in the
ThisWorkbook object instead:

Private Sub Workbook_SheetChange(ByVal Sh As Object, _
ByVal Target As Range)
Dim cell As Range, outP As String, costs As Variant
costs = Array("#", "A", "B", "C", "D", "F", "#", "#", "#", "#")
For Each cell In Target
If cell.Column = 1 Then
outP = ""
c = Abs(Val(cell.Value))
Do While c 0
n = c Mod 10
c = c \ 10
outP = costs(n) & outP
Loop
Sh.Cells(cell.Row, cell.Column + 1).Value = outP
End If
Next
End Sub

--
Money and faith are powerful motivators.

Moideen
Quote:
Originally Posted by Auric__ (Post 1603281)
Moideen wrote:

We are Maintaining cost in English Letters, Kindly Help to get COST on
Coloumn2

Examples,

A : 1
B : 2
C : 3
D : 4
F : 5

If I Entered 242 on Coloumn1 Need Automatic Shown BDB on Coloumn2

Put this in the sheet's object in the VBA editor:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range, outP As String, costs As Variant
costs = Array("#", "A", "B", "C", "D", "F", "#", "#", "#", "#")
For Each cell In Target
If cell.Column = 1 Then
outP = ""
c = Abs(Val(cell.Value))
Do While c 0
n = c Mod 10
c = c \ 10
outP = costs(n) & outP
Loop
Me.Cells(cell.Row, cell.Column + 1).Value = outP
End If
Next
End Sub

Edit the 'costs' array to fit. (The hashes are there to indicate data entry
errors. If you don't want them, don't delete them -- change them to "".)

If this is meant to apply to the entire workbook, put this in the
ThisWorkbook object instead:

Private Sub Workbook_SheetChange(ByVal Sh As Object, _
ByVal Target As Range)
Dim cell As Range, outP As String, costs As Variant
costs = Array("#", "A", "B", "C", "D", "F", "#", "#", "#", "#")
For Each cell In Target
If cell.Column = 1 Then
outP = ""
c = Abs(Val(cell.Value))
Do While c 0
n = c Mod 10
c = c \ 10
outP = costs(n) & outP
Loop
Sh.Cells(cell.Row, cell.Column + 1).Value = outP
End If
Next
End Sub

--
Money and faith are powerful motivators.
Dear Auric,

Thank you very much, This function working is smoothly but one problem,
If i entered 12.3 , Need Letter : AB.C or AB/C Please Help me.

Auric__
To be got Alphabetic
 
Moideen wrote:

Auric__ Wrote:
Moideen wrote:
-
We are Maintaining cost in English Letters, Kindly Help to get COST
on Coloumn2

Examples,

A : 1
B : 2
C : 3
D : 4
F : 5

If I Entered 242 on Coloumn1 Need Automatic Shown BDB on Coloumn2-

Put this in the sheet's object in the VBA editor:
[snip]
Edit the 'costs' array to fit. (The hashes are there to indicate data
entry errors. If you don't want them, don't delete them -- change them
to "".)

If this is meant to apply to the entire workbook, put this in the
ThisWorkbook object instead:
[snip]
Thank you very much, This function working is smoothly but one problem,
If i entered 12.3 , Need Letter : AB.C or AB/C Please Help me.

I looked through the functions, and found the SUBSTITUTE spreadsheet
function. It should be faster than my code, and should also be easier to
understand. Paste this into B1 and then copy down (one line, watch the word
wrap):

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

This can be extended by adding on more levels of SUBSTITUTE if necessary.

If you'd rather stick with VBA, this works similarly:

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(cell.Value, 1, "A"), 2, "B"), 3, "C"), _
4, "D"), 5, "F")
Next
End Sub

(Forget about what I posted before. Wasted effort on my part, mostly.)

--
I don't believe in *lots* of invisible things
that are supposed to make me happy.

Moideen
Quote:
Originally Posted by Auric__ (Post 1603287)
Moideen wrote:

Auric__ Wrote:
Moideen wrote:
-
We are Maintaining cost in English Letters, Kindly Help to get COST
on Coloumn2

Examples,

A : 1
B : 2
C : 3
D : 4
F : 5

If I Entered 242 on Coloumn1 Need Automatic Shown BDB on Coloumn2-

Put this in the sheet's object in the VBA editor:
[snip]
Edit the 'costs' array to fit. (The hashes are there to indicate data
entry errors. If you don't want them, don't delete them -- change them
to "".)

If this is meant to apply to the entire workbook, put this in the
ThisWorkbook object instead:
[snip]
Thank you very much, This function working is smoothly but one problem,
If i entered 12.3 , Need Letter : AB.C or AB/C Please Help me.

I looked through the functions, and found the SUBSTITUTE spreadsheet
function. It should be faster than my code, and should also be easier to
understand. Paste this into B1 and then copy down (one line, watch the word
wrap):

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

This can be extended by adding on more levels of SUBSTITUTE if necessary.

If you'd rather stick with VBA, this works similarly:

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(cell.Value, 1, "A"), 2, "B"), 3, "C"), _
4, "D"), 5, "F")
Next
End Sub

(Forget about what I posted before. Wasted effort on my part, mostly.)

--
I don't believe in *lots* of invisible things
that are supposed to make me happy.
Thanks a lot...

Moideen
Quote:
Originally Posted by Moideen (Post 1603299)
Thanks a lot...
Dear Auric,

I Tried with the below mentioned VBA code "0" not showing.

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

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(cell.Value, 1, "A"), 2, "B"), 3, "C"), _
4, "D"), 5, "F"), 0, "S")
Next
End Sub

Ron Rosenfeld[_2_]
To be got Alphabetic
 
On Mon, 2 Jul 2012 14:49:01 +0000, Moideen wrote:

Dear Auric,

I Tried with the below mentioned VBA code "0" not showing.

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

That makes perfect sense, given the specifications you have provided.

================
A : 1
B : 2
C : 3
D : 4
F : 5
================================

It seems that you have only provided letter values for the numerals 1 to 5. And in a later post you indicated that you wanted the decimal (.) to be preserved.
Why would you expect any other numerals to be taken into account?

If you provide incomplete specifications, you should not be surprised that the results do not take into account requirements that you do not specify. I would suggest that, if there are other digits that you want to convert to letters, you include ALL of them in a single post.

Auric__
To be got Alphabetic
 
Moideen wrote:

I Tried with the below mentioned VBA code "0" not showing.

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

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(cell.Value, 1, "A"), 2, "B"), 3, "C"), _
4, "D"), 5, "F"), 0, "S")
Next
End Sub

As I said before, you can extend the function by adding more levels of
SUBSTITUTE or Replace as necessary -- meaning you need to *actually add
another copy of the keyword*, not just the info to be replaced:

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

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")

If you need a seventh replacement, you need a seventh SUBSTITUTE/Replace...
but this is going to get unwieldy pretty fast, especially if you're replacing
*every* digit with a letter.

If you have more than this, you should go back to looking the digits up in an
array, similar (but not identical) to my first reply.

--
Here is an idea for you geniuses:
Point your weapons at the bugs and shoot.
If they die, the guns work. If not, grab a brick.

Moideen
Quote:
Originally Posted by Auric__ (Post 1603344)
Moideen wrote:

I Tried with the below mentioned VBA code "0" not showing.

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

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(cell.Value, 1, "A"), 2, "B"), 3, "C"), _
4, "D"), 5, "F"), 0, "S")
Next
End Sub

As I said before, you can extend the function by adding more levels of
SUBSTITUTE or Replace as necessary -- meaning you need to *actually add
another copy of the keyword*, not just the info to be replaced:

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

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")

If you need a seventh replacement, you need a seventh SUBSTITUTE/Replace...
but this is going to get unwieldy pretty fast, especially if you're replacing
*every* digit with a letter.

If you have more than this, you should go back to looking the digits up in an
array, similar (but not identical) to my first reply.

--
Here is an idea for you geniuses:
Point your weapons at the bugs and shoot.
If they die, the guns work. If not, grab a brick.
Dear Auric,

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")

Auric__
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?

Moideen
Quote:
Originally Posted by Auric__ (Post 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

--
Where is your savior now?
Not Getting..

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

Moideen
[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

Auric__
To be got Alphabetic
 
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.

Auric__
To be got Alphabetic
 
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.

Moideen
Quote:
Originally Posted by Auric__ (Post 1603384)
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.


All times are GMT +1. The time now is 02:51 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com