Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Getting the format string from a cell

Posted on 2011-03-25
3
Medium Priority
?
235 Views
Last Modified: 2012-05-11
How can I get the format string of a cell?

I need to get the format string of a cell, something like "#,##0.00_);[Red]($#,##0.00)" so I can use it in a formula like =Text(INDEX(A249:AJ249,0,$D$2),FormatString(INDEX(A249:AJ249,0,$D$2))), so the values are display using the same format as the referenced one.
0
Comment
Question by:javier-d
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
3 Comments
 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 2000 total points
ID: 35218533
You should be able to use the archaic GET.CELL function. Specifically:

   =GET.CELL(7+NOW()*0,Sheet1!A1)

To use the GET.CELL function in a worksheet formula it must be placed in a defined name. Since Excel does not track dependencies in named formulas an additional function must be added to force recalculations when necessary - in the examples below the formula "NOW()*0" is added to the GET.CELL request parameter. Below are some examples of using the GET.CELL function.

Getting a cell's interior color index:

   =GET.CELL(63+NOW()*0,Sheet1!A1)

Getting a cell's font color index (first character only):

   =GET.CELL(24+NOW()*0,Sheet1!A1)

Getting the cell's text as displayed:

   =GET.CELL(53+NOW()*0,Sheet1!A1)

Below are all of the type numbers that can be used with the GET.CELL function.

1 - Absolute reference of the upper-left cell in reference, as text in the current workspace reference style.

2 - Row number of the top cell in reference.

3 - Column number of the leftmost cell in reference.

4 - Same as TYPE(reference).

5 - Contents of reference.

6 - Formula in reference, as text, in either A1 or R1C1 style depending on the workspace setting.

7 - Number format of the cell as text (for example, "m/d/yy" or "General").

8 - Number indicating the cell's horizontal alignment: 1 = General, 2 = Left, 3 = Center, 4 = Right, 5 = Fill, 6 = Justify, 7 = Center across cells.

9 - Number indicating the left-border style assigned to the cell: 0 = No border, 1 = Thin line, 2 = Medium line, 3 = Dashed line, 4 = Dotted line, 5 = Thick line, 6 = Double line, 7 = Hairline.

10 - Number indicating the right-border style assigned to the cell. See type_num 9 for descriptions of the numbers returned.

11 - Number indicating the top-border style assigned to the cell. See type_num 9 for descriptions of the numbers returned.

12 - Number indicating the bottom-border style assigned to the cell. See type_num 9 for descriptions of the numbers returned.

13 - Number from 0 to 18, indicating the pattern of the selected cell as displayed in the Patterns tab of the Format Cells dialog box, which appears when you choose the Cells command from the Format menu. If no pattern is selected, returns 0.

14 - If the cell is locked, returns TRUE; otherwise, returns FALSE.

15 - If the cell's formula is hidden, returns TRUE; otherwise, returns FALSE.

16 - A two-item horizontal array containing the width of the active cell and a logical value indicating whether the cell's width is set to change as the standard width changes (TRUE) or is a custom width (FALSE).

17 - Row height of cell, in points.

18 - Name of font, as text.

19 - Size of font, in points.

20 - If all the characters in the cell, or only the first character, are bold, returns TRUE; otherwise, returns FALSE.

21 - If all the characters in the cell, or only the first character, are italic, returns TRUE; otherwise, returns FALSE.

22 - If all the characters in the cell, or only the first character, are underlined, returns TRUE; otherwise, returns FALSE.

23 - If all the characters in the cell, or only the first character, are struck through, returns TRUE; otherwise, returns FALSE.

24 - Font color of the first character in the cell, as a number in the range 1 to 56. If font color is automatic, returns 0.

25 - If all the characters in the cell, or only the first character, are outlined, returns TRUE; otherwise, returns FALSE. Outline font format is not supported by Microsoft Excel for Windows.

26 - If all the characters in the cell, or only the first character, are shadowed, returns TRUE; otherwise, returns FALSE. Shadow font format is not supported by Microsoft Excel for Windows.

27 - Number indicating whether a manual page break occurs at the cell: 0 = No break 1 = Row 2 = Column 3 = Both row and column.

28 - Row level (outline).

29 - Column level (outline).

30 - If the row containing the active cell is a summary row, returns TRUE; otherwise, returns FALSE.

31 - If the column containing the active cell is a summary column, returns TRUE; otherwise, returns FALSE.

32 - Name of the workbook and sheet containing the cell If the window contains only a single sheet that has the same name as the workbook without its extension, returns only the name of the book, in the form BOOK1.XLS. Otherwise, returns the name of the sheet in the form "[Book1]Sheet1".

33 - If the cell is formatted to wrap, returns TRUE; otherwise, returns FALSE.

34 - Left-border color as a number in the range 1 to 56. If color is automatic, returns 0.

35 - Right-border color as a number in the range 1 to 56. If color is automatic, returns 0.

36 - Top-border color as a number in the range 1 to 56. If color is automatic, returns 0.

37 - Bottom-border color as a number in the range 1 to 56. If color is automatic, returns 0.

38 - Shade foreground color as a number in the range 1 to 56. If color is automatic, returns 0.

39 - Shade background color as a number in the range 1 to 56. If color is automatic, returns 0.

40 - Style of the cell, as text.

41 - Returns the formula in the active cell without translating it (useful for international macro sheets).

42 - The horizontal distance, measured in points, from the left edge of the active window to the left edge of the cell. May be a negative number if the window is scrolled beyond the cell.

43 - The vertical distance, measured in points, from the top edge of the active window to the top edge of the cell. May be a negative number if the window is scrolled beyond the cell.

44 - The horizontal distance, measured in points, from the left edge of the active window to the right edge of the cell. May be a negative number if the window is scrolled beyond the cell.

45 - The vertical distance, measured in points, from the top edge of the active window to the bottom edge of the cell. May be a negative number if the window is scrolled beyond the cell.

46 - If the cell contains a text note, returns TRUE; otherwise, returns FALSE.

47 - If the cell contains a sound note, returns TRUE; otherwise, returns FALSE.

48 - If the cells contains a formula, returns TRUE; if a constant, returns FALSE.

49 - If the cell is part of an array, returns TRUE; otherwise, returns FALSE.

50 - Number indicating the cell's vertical alignment: 1 = Top, 2 = Center, 3 = Bottom, 4 = Justified.

51 - Number indicating the cell's vertical orientation: 0 = Horizontal, 1 = Vertical, 2 = Upward, 3 = Downward.

52 - The cell prefix (or text alignment) character, or empty text ("") if the cell does not contain one.

53 - Contents of the cell as it is currently displayed, as text, including any additional numbers or symbols resulting from the cell's formatting.

54 - Returns the name of the PivotTable containing the active cell.

55 - Returns the position of a cell within the PivotTable: 0 = Row header, 1 = Column header, 2 = Page header, 3 = Data header, 4 = Row item, 5 = Column item, 6 = Page item, 7 = Data item, 8 = Table body.

56 - Returns the name of the field containing the active cell reference if inside a PivotTable.

57 - Returns TRUE if all the characters in the cell, or only the first character, are formatted with a superscript font; otherwise, returns FALSE.

58 - Returns the font style as text of all the characters in the cell, or only the first character as displayed in the Font tab of the Format Cells dialog box: for example, "Bold Italic".

59 - Returns the number for the underline style: 1 = none, 2 = single, 3 = double, 4 = single accounting, 5 = double accounting.

60 - Returns TRUE if all the characters in the cell, or only the first character, are formatted with a subscript font; otherwise, it returns FALSE.

61 - Returns the name of the PivotTable item for the active cell, as text.

62 - Returns the name of the workbook and the current sheet in the form "[Book1]Sheet1".

63 - Returns the fill (background) color of the cell.

64 - Returns the pattern (foreground) color of the cell.

65 - Returns TRUE if the Add Indent alignment option is on (Far East versions of Microsoft Excel only); otherwise, it returns FALSE.

66 - Returns the book name of the workbook containing the cell in the form BOOK1.XLS.

Kevin
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35218606
An example.

Kevin
Q-26912564.xls
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35218719
If you don't mind some VBA code behind the workbook then you can use this UDF:

Public Function CellFormat(ByVal Cell As Range) As Variant
    Application.Volatile
    CellFormat = Cell.NumberFormat
End Function

Once placed in a general code module you can use it in a formula like so:

   =Text(INDEX(A249:AJ249,0,$D$2),CellFormat(INDEX(A249:AJ249,0,$D$2)))

Kevin
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question