TEXT function cannot use CELL function format codes

tyannotti

New member
Joined
Apr 15, 2014
Messages
7
Reaction score
0
Points
1
The cell format codes returned by the CELL function does not work inside of a TEXT function.

For example, the contents of my cells are:


A1: 50000
A2: =CELL("format",A1)
A3: =TEXT(A1,CELL("format",A1))
A4: =TEXT(A1,".00")


and the cells return (with comments):



A1: 50000.00 (cell is formatted as a 2-decimal number)
A2: F2 (the cell format code for 2-decimal number)
A3: F2 (does not return correct value "50000.00")
A4: 50000.00 (so..."F2" is not the same a ".00")


So, how do I do this?

Conditional formatting won't work because you only get 3 conditions.
A manual recalc (F9 key) didn't fix it.
VBA or Macros won't work because no one will launch or hit button (etc.)



We need this because the person entering data may need to change the entry cells to different formats for different types of checks (payroll vs. reimbursement vs. vendor check...etc).

We need to automatically change the format of the output cells to match the format of the entry cells (which the person is allowed to change the format of).


Again, conditional formatting won't work...too few choices. And we don't know how many different formats we will need.


Thanks
Tony
 

Attachments

  • TEST Text Function.xlsx
    9 KB · Views: 23

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,942
Reaction score
0
Points
36
Excel Version(s)
O365
Why do you want to get a text value in the format of the cell itself? Seems rather pointless to me.
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,942
Reaction score
0
Points
36
Excel Version(s)
O365
BTW, CF has unlimited conditions.
 

tyannotti

New member
Joined
Apr 15, 2014
Messages
7
Reaction score
0
Points
1
BTW, CF has unlimited conditions.

Thanks Bob,

The data-entry cells are the only cells where we need numbers (or data, etc).


The output cells are for display only (and are shown on the check and on the attached stub).


Different types of checks do not need different formats in their respective data-entry cells. However, the output cells should be formatted so it prints out and looks okay. CF probably won't work because there is nothing unique about the numbers (except the format)

Again, thanks
Tny
 

tyannotti

New member
Joined
Apr 15, 2014
Messages
7
Reaction score
0
Points
1
Thanks Bob,

The data-entry cells are the only cells where we need numbers (or data, etc).


The output cells are for display only (and are shown on the check and on the attached stub).


Different types of checks do not need different formats in their respective data-entry cells. However, the output cells should be formatted so it prints out and looks okay. CF probably won't work because there is nothing unique about the numbers (except the format)

Again, thanks
Tny


Maybe you are not understanding...the snippet above is an example only...the actual spreadsheet is quite different, much larger
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,942
Reaction score
0
Points
36
Excel Version(s)
O365
I am sorry, I don't see any clarification in your last comment. I looked at the workbook. cell A1 was formatted to 2 dec places, and you seemed to want to create a text string that had the same format. Again, I cannot see why you would do that.
 

tyannotti

New member
Joined
Apr 15, 2014
Messages
7
Reaction score
0
Points
1
I am sorry, I don't see any clarification in your last comment. I looked at the workbook. cell A1 was formatted to 2 dec places, and you seemed to want to create a text string that had the same format. Again, I cannot see why you would do that.

Bob,

I am very glad you are taking the time to answer this...so let me try to clarify.
The file is only a snippet or example.
The actual spreadsheet (which is a check writing template) has data-entry cells. The information in the data-entry cells are migrated to several locations which then fill in the check. I have to format the data-entry areas for each type of check I am writing (payroll, reimbursement, expenses, contractors, etc). The output cells (which fill in the blanks) need to be re-formatted to be the same as the data-entry cells. I used to re-format by hand, each time.
A blank, preprinted check is put into a laser printer and has three perforated parts - the check and two stubs.

I included a sample file (with personal information deleted). The areas near the red lettering is where I insert the data.
 

Attachments

  • ChecksSAMPLE.xlsx
    105.3 KB · Views: 26

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,942
Reaction score
0
Points
36
Excel Version(s)
O365
Tony,

I am sorry, I am really not being awkward, I would like to help, but I am still confused. I can understand why you want the summary to be the same format as the cheques, but won't it always be $ to 2 decimal places. In other words, why do you need to interrogate a cell for its format, don't you know it up-front? So I still cannot see what you need to do beyond what you show in this example; you have the cheques all filled out with all moneys in $#,##0.00 format, then you have the summary referencing the cheque amount formatted as $#,##0.00.

BTW, how are you getting the spelled-out amount, as in

Personally, I would just hold a simple table of cheques, #, constituent amounts etc., and then use VBA to build a formatted cheque onto a template sheet for printing.
 

tyannotti

New member
Joined
Apr 15, 2014
Messages
7
Reaction score
0
Points
1
Tony,

I am sorry, I am really not being awkward, I would like to help, but I am still confused. I can understand why you want the summary to be the same format as the cheques, but won't it always be $ to 2 decimal places. In other words, why do you need to interrogate a cell for its format, don't you know it up-front? So I still cannot see what you need to do beyond what you show in this example; you have the cheques all filled out with all moneys in $#,##0.00 format, then you have the summary referencing the cheque amount formatted as $#,##0.00.

BTW, how are you getting the spelled-out amount, as in

Personally, I would just hold a simple table of cheques, #, constituent amounts etc., and then use VBA to build a formatted cheque onto a template sheet for printing.



Any chance you can go back to the original question?
All I am trying to figure out is...
I want to change the format of a cell and have the linked cells (is that the correct term?) change to the same format.

For example...I change a cell from a date format to a number format. The cells that are referencing that cell would automatically adopt that format - so they are now the same format.
 
Top