Text format & number format

S

Slashman

Hi,

I have a cell that is formatted with a macro to change the number 10 to
+10.0000

I would also like to add a text letter of g to this same cell so it
reads +10.0000g

When I try and use the & function in the formula it just gives me 100g
and removes the + formatting and the trailing DP zeroes.

Is there a way of combining these?

Thanks in advance,

Aaron.
 
R

Ron Rosenfeld

Hi,

I have a cell that is formatted with a macro to change the number 10 to
+10.0000

I would also like to add a text letter of g to this same cell so it
reads +10.0000g

When I try and use the & function in the formula it just gives me 100g
and removes the + formatting and the trailing DP zeroes.

Is there a way of combining these?

Thanks in advance,

Aaron.
Click to expand...

Cannot be more specific since you chose not to post the macro you are using.
However, in general, you can accomplish what you want by adding a \g to the
format string (after the decimal zeros) within the macro.

In other words, if the format string in the macro is something like:

"+0.0000_)", you could change it to "+0.0000\g_)"


--ron
 
S

Slashman

Hi Ron

I didn't post the macro because I thought it might confuse more.

///////////////////////////////////////////////////////////////////////////////////////////////////////////

Dim KeyCell As Range, AnswerCell As Range
Dim DP As Long
Dim kcFmt As String
Dim acFmt As String

Set KeyCell = [q16]
Set AnswerCell = [x4]

'Get DP from Key Cell
kcFmt = KeyCell.NumberFormat

DP = Len(kcFmt) - InStr(1, kcFmt, ".") + 0 'add a number here to apply
more DP

'Add one decimal place to the KeyCell number format
acFmt = "0." & Application.WorksheetFunction.Rept("0", DP)

'special case of kcFmt is "0"
If kcFmt = "0" Then acFmt = "0.0"

acFmt = "+" & acFmt & ";-" & acFmt & ";0"

'Assign new format to answer cell
AnswerCell.NumberFormat = acFmt

//////////////////////////////////////////////////////////////////////////////////////////////////

I have tried going into format and then custom and adding the \g after
the code and it works nicely.

EG: +0.000\g;-0.000\g;0\g

But the other thing I forgot to mention, is that I sometimes have kg
and N, not always g, how can I automate for this? There is a cell, q17
that will have the sheets g or kg or N in it though if this helps.

Thanks,

Aaron.
 
S

Slashman

Ok, in debug mode, acFmt looks correct, but I get the message unable to
set the range class.

I added these g, kg or N cell info to the acFmt line:

acFmt = "+" & acFmt & [q17] & ";-" & acFmt & [q17] & ";0" & [q17]

'Assign new format to answer cell
AnswerCell.NumberFormat = acFmt

Cheers,

Aaron.

Hi Ron

I didn't post the macro because I thought it might confuse more.

///////////////////////////////////////////////////////////////////////////////////////////////////////////

Dim KeyCell As Range, AnswerCell As Range
Dim DP As Long
Dim kcFmt As String
Dim acFmt As String

Set KeyCell = [q16]
Set AnswerCell = [x4]

'Get DP from Key Cell
kcFmt = KeyCell.NumberFormat

DP = Len(kcFmt) - InStr(1, kcFmt, ".") + 0 'add a number here to apply
more DP

'Add one decimal place to the KeyCell number format
acFmt = "0." & Application.WorksheetFunction.Rept("0", DP)

'special case of kcFmt is "0"
If kcFmt = "0" Then acFmt = "0.0"

acFmt = "+" & acFmt & ";-" & acFmt & ";0"

'Assign new format to answer cell
AnswerCell.NumberFormat = acFmt

//////////////////////////////////////////////////////////////////////////////////////////////////

I have tried going into format and then custom and adding the \g after
the code and it works nicely.

EG: +0.000\g;-0.000\g;0\g

But the other thing I forgot to mention, is that I sometimes have kg
and N, not always g, how can I automate for this? There is a cell, q17
that will have the sheets g or kg or N in it though if this helps.

Thanks,

Aaron.

Ron said:
Cannot be more specific since you chose not to post the macro you are using.
However, in general, you can accomplish what you want by adding a \g to the
format string (after the decimal zeros) within the macro.

In other words, if the format string in the macro is something like:

"+0.0000_)", you could change it to "+0.0000\g_)"


--ron
Click to expand...
Click to expand...
 
B

Bob Phillips

What's in q17?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Slashman said:
Ok, in debug mode, acFmt looks correct, but I get the message unable to
set the range class.

I added these g, kg or N cell info to the acFmt line:

acFmt = "+" & acFmt & [q17] & ";-" & acFmt & [q17] & ";0" & [q17]

'Assign new format to answer cell
AnswerCell.NumberFormat = acFmt

Cheers,

Aaron.

Hi Ron

I didn't post the macro because I thought it might confuse more.
Click to expand...
////////////////////////////////////////////////////////////////////////////
///////////////////////////////
Dim KeyCell As Range, AnswerCell As Range
Dim DP As Long
Dim kcFmt As String
Dim acFmt As String

Set KeyCell = [q16]
Set AnswerCell = [x4]

'Get DP from Key Cell
kcFmt = KeyCell.NumberFormat

DP = Len(kcFmt) - InStr(1, kcFmt, ".") + 0 'add a number here to apply
more DP

'Add one decimal place to the KeyCell number format
acFmt = "0." & Application.WorksheetFunction.Rept("0", DP)

'special case of kcFmt is "0"
If kcFmt = "0" Then acFmt = "0.0"

acFmt = "+" & acFmt & ";-" & acFmt & ";0"

'Assign new format to answer cell
AnswerCell.NumberFormat = acFmt
Click to expand...
////////////////////////////////////////////////////////////////////////////
//////////////////////
I have tried going into format and then custom and adding the \g after
the code and it works nicely.

EG: +0.000\g;-0.000\g;0\g

But the other thing I forgot to mention, is that I sometimes have kg
and N, not always g, how can I automate for this? There is a cell, q17
that will have the sheets g or kg or N in it though if this helps.

Thanks,

Aaron.

Click to expand...
Click to expand...
 
B

Bob Phillips

maybe this is what you want

Dim KeyCell As Range, AnswerCell As Range
Dim DP As Long
Dim kcFmt As String
Dim acFmt As String
Dim suffix As String

Set KeyCell = [q16]
Set AnswerCell = [x4]

'Get DP from Key Cell
kcFmt = KeyCell.NumberFormat

DP = Len(kcFmt) - InStr(1, kcFmt, ".") + 0 'add a number here to apply more
DP

'Add one decimal place to the KeyCell number format
acFmt = "0." & Application.WorksheetFunction.Rept("0", DP)

'special case of kcFmt is "0"
If kcFmt = "0" Then acFmt = "0.0"

'acFmt = "+" & acFmt & ";-" & acFmt & ";0"

If Range("Q17").Value >= 1000 Then
suffix = """kg"""
ElseIf Range("Q17").Value >= 1000000 Then
suffix = "\N"
Else
suffix = "\g"
End If
acFmt = "+" & acFmt & suffix & ";-" & acFmt & suffix & ";0" & suffix

'Assign new format to answer cell
AnswerCell.NumberFormat = acFmt



--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Slashman said:
Ok, in debug mode, acFmt looks correct, but I get the message unable to
set the range class.

I added these g, kg or N cell info to the acFmt line:

acFmt = "+" & acFmt & [q17] & ";-" & acFmt & [q17] & ";0" & [q17]

'Assign new format to answer cell
AnswerCell.NumberFormat = acFmt

Cheers,

Aaron.

Hi Ron

I didn't post the macro because I thought it might confuse more.
Click to expand...
////////////////////////////////////////////////////////////////////////////
///////////////////////////////
Dim KeyCell As Range, AnswerCell As Range
Dim DP As Long
Dim kcFmt As String
Dim acFmt As String

Set KeyCell = [q16]
Set AnswerCell = [x4]

'Get DP from Key Cell
kcFmt = KeyCell.NumberFormat

DP = Len(kcFmt) - InStr(1, kcFmt, ".") + 0 'add a number here to apply
more DP

'Add one decimal place to the KeyCell number format
acFmt = "0." & Application.WorksheetFunction.Rept("0", DP)

'special case of kcFmt is "0"
If kcFmt = "0" Then acFmt = "0.0"

acFmt = "+" & acFmt & ";-" & acFmt & ";0"

'Assign new format to answer cell
AnswerCell.NumberFormat = acFmt
Click to expand...
////////////////////////////////////////////////////////////////////////////
//////////////////////
I have tried going into format and then custom and adding the \g after
the code and it works nicely.

EG: +0.000\g;-0.000\g;0\g

But the other thing I forgot to mention, is that I sometimes have kg
and N, not always g, how can I automate for this? There is a cell, q17
that will have the sheets g or kg or N in it though if this helps.

Thanks,

Aaron.

Click to expand...
Click to expand...
 
R

Ron Rosenfeld

Hi Ron

I didn't post the macro because I thought it might confuse more.

///////////////////////////////////////////////////////////////////////////////////////////////////////////

Dim KeyCell As Range, AnswerCell As Range
Dim DP As Long
Dim kcFmt As String
Dim acFmt As String

Set KeyCell = [q16]
Set AnswerCell = [x4]

'Get DP from Key Cell
kcFmt = KeyCell.NumberFormat

DP = Len(kcFmt) - InStr(1, kcFmt, ".") + 0 'add a number here to apply
more DP

'Add one decimal place to the KeyCell number format
acFmt = "0." & Application.WorksheetFunction.Rept("0", DP)

'special case of kcFmt is "0"
If kcFmt = "0" Then acFmt = "0.0"

acFmt = "+" & acFmt & ";-" & acFmt & ";0"

'Assign new format to answer cell
AnswerCell.NumberFormat = acFmt

//////////////////////////////////////////////////////////////////////////////////////////////////

I have tried going into format and then custom and adding the \g after
the code and it works nicely.

EG: +0.000\g;-0.000\g;0\g

But the other thing I forgot to mention, is that I sometimes have kg
and N, not always g, how can I automate for this? There is a cell, q17
that will have the sheets g or kg or N in it though if this helps.

Thanks,

Aaron.
Click to expand...

That is the macro I gave you :)

acFmt = "+" & acFmt & [q17] & ";-" & acFmt & [q17] & ";0" & [q17]
Click to expand...

You left out the "\". However, that "\" will only work for single characters
and, unlike your initial post, you now write that you may have two characters
to add on. So that needs to be entered within double quote marks.

I think the simplest way to do that, since you have the desired suffix in
[q17], would be as below.

Change the references to KeyCell, AnswerCell and SuffixCell as required. And,
depending on how you set things up, you may have to add a line to reference the
proper worksheet. As written, the macro will run on the Active Worksheet.


==========================================
Option Explicit

Sub IncrDP()

Dim KeyCell As Range, AnswerCell As Range
Dim SuffixCell As Range
Dim DP As Long
Dim kcFmt As String
Dim acFmt As String
Dim Suffix As String

Set KeyCell = [a1]
Set AnswerCell = [a2]
Set SuffixCell = [a3]

'Note the quotes within the quotes.
Suffix = """" & SuffixCell.Text & """"

'Get DP from Key Cell
kcFmt = KeyCell.NumberFormat

DP = Len(kcFmt) - InStr(1, kcFmt, ".") + 1

'Add one decimal place to the KeyCell number format
acFmt = "0." & Application.WorksheetFunction.Rept("0", DP) & Suffix

'special case of kcFmt is "0"
If kcFmt = "0" Then acFmt = "0.0" & Suffix

acFmt = "+" & acFmt & ";-" & acFmt & ";0"

'Assign new format to answer cell
AnswerCell.NumberFormat = acFmt

End Sub
==================================
--ron
 
S

Slashman

Hi Bob,

in q17 is the text that could be either N g or kg, but in x4 the answer
could be anything at all any number at all so the if and elseif wont
work. But it gave me an understanding of how that works.

Cheers,

Aaron.

Bob said:
maybe this is what you want

Dim KeyCell As Range, AnswerCell As Range
Dim DP As Long
Dim kcFmt As String
Dim acFmt As String
Dim suffix As String

Set KeyCell = [q16]
Set AnswerCell = [x4]

'Get DP from Key Cell
kcFmt = KeyCell.NumberFormat

DP = Len(kcFmt) - InStr(1, kcFmt, ".") + 0 'add a number here to apply more
DP

'Add one decimal place to the KeyCell number format
acFmt = "0." & Application.WorksheetFunction.Rept("0", DP)

'special case of kcFmt is "0"
If kcFmt = "0" Then acFmt = "0.0"

'acFmt = "+" & acFmt & ";-" & acFmt & ";0"

If Range("Q17").Value >= 1000 Then
suffix = """kg"""
ElseIf Range("Q17").Value >= 1000000 Then
suffix = "\N"
Else
suffix = "\g"
End If
acFmt = "+" & acFmt & suffix & ";-" & acFmt & suffix & ";0" & suffix

'Assign new format to answer cell
AnswerCell.NumberFormat = acFmt



--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Slashman said:
Ok, in debug mode, acFmt looks correct, but I get the message unable to
set the range class.

I added these g, kg or N cell info to the acFmt line:

acFmt = "+" & acFmt & [q17] & ";-" & acFmt & [q17] & ";0" & [q17]

'Assign new format to answer cell
AnswerCell.NumberFormat = acFmt

Cheers,

Aaron.

Hi Ron

I didn't post the macro because I thought it might confuse more.
Click to expand...
////////////////////////////////////////////////////////////////////////////
///////////////////////////////
Dim KeyCell As Range, AnswerCell As Range
Dim DP As Long
Dim kcFmt As String
Dim acFmt As String

Set KeyCell = [q16]
Set AnswerCell = [x4]

'Get DP from Key Cell
kcFmt = KeyCell.NumberFormat

DP = Len(kcFmt) - InStr(1, kcFmt, ".") + 0 'add a number here to apply
more DP

'Add one decimal place to the KeyCell number format
acFmt = "0." & Application.WorksheetFunction.Rept("0", DP)

'special case of kcFmt is "0"
If kcFmt = "0" Then acFmt = "0.0"

acFmt = "+" & acFmt & ";-" & acFmt & ";0"

'Assign new format to answer cell
AnswerCell.NumberFormat = acFmt
Click to expand...
////////////////////////////////////////////////////////////////////////////
//////////////////////
Click to expand...
Click to expand...
 
S

Slashman

Hi Ron,

Your modified original Macro works well. And has solved another prob.

Thanks alot,

Aaron.

Ron said:
Hi Ron

I didn't post the macro because I thought it might confuse more.

///////////////////////////////////////////////////////////////////////////////////////////////////////////

Dim KeyCell As Range, AnswerCell As Range
Dim DP As Long
Dim kcFmt As String
Dim acFmt As String

Set KeyCell = [q16]
Set AnswerCell = [x4]

'Get DP from Key Cell
kcFmt = KeyCell.NumberFormat

DP = Len(kcFmt) - InStr(1, kcFmt, ".") + 0 'add a number here to apply
more DP

'Add one decimal place to the KeyCell number format
acFmt = "0." & Application.WorksheetFunction.Rept("0", DP)

'special case of kcFmt is "0"
If kcFmt = "0" Then acFmt = "0.0"

acFmt = "+" & acFmt & ";-" & acFmt & ";0"

'Assign new format to answer cell
AnswerCell.NumberFormat = acFmt

//////////////////////////////////////////////////////////////////////////////////////////////////

I have tried going into format and then custom and adding the \g after
the code and it works nicely.

EG: +0.000\g;-0.000\g;0\g

But the other thing I forgot to mention, is that I sometimes have kg
and N, not always g, how can I automate for this? There is a cell, q17
that will have the sheets g or kg or N in it though if this helps.

Thanks,

Aaron.
Click to expand...

That is the macro I gave you :)

acFmt = "+" & acFmt & [q17] & ";-" & acFmt & [q17] & ";0" & [q17]
Click to expand...

You left out the "\". However, that "\" will only work for single characters
and, unlike your initial post, you now write that you may have two characters
to add on. So that needs to be entered within double quote marks.

I think the simplest way to do that, since you have the desired suffix in
[q17], would be as below.

Change the references to KeyCell, AnswerCell and SuffixCell as required. And,
depending on how you set things up, you may have to add a line to reference the
proper worksheet. As written, the macro will run on the Active Worksheet.


==========================================
Option Explicit

Sub IncrDP()

Dim KeyCell As Range, AnswerCell As Range
Dim SuffixCell As Range
Dim DP As Long
Dim kcFmt As String
Dim acFmt As String
Dim Suffix As String

Set KeyCell = [a1]
Set AnswerCell = [a2]
Set SuffixCell = [a3]

'Note the quotes within the quotes.
Suffix = """" & SuffixCell.Text & """"

'Get DP from Key Cell
kcFmt = KeyCell.NumberFormat

DP = Len(kcFmt) - InStr(1, kcFmt, ".") + 1

'Add one decimal place to the KeyCell number format
acFmt = "0." & Application.WorksheetFunction.Rept("0", DP) & Suffix

'special case of kcFmt is "0"
If kcFmt = "0" Then acFmt = "0.0" & Suffix

acFmt = "+" & acFmt & ";-" & acFmt & ";0"

'Assign new format to answer cell
AnswerCell.NumberFormat = acFmt

End Sub
==================================
--ron
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel does not accept text format! 0
Remove numbers from brackets 0
Address Format changed after Migration 0
Paste to excel from word - auto format problem 1
Transfer data between two tables 3
Tracking text changes 0
Custom Number sequences 0
Number Format 4

Top