Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
text alignment settings
Is there a way to get Excel to return the alignment settings of a cell ? I
use the CELL function extensively but it only returns limited information. I have a spreadsheet provided by a client where I need to extract all rows which are left aligned with an indent of 12, but I can't find a function that will return such information to me so that I can do this extract. Any assistance would be appreciated. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
text alignment settings
You can try the below UDF.
If you are new to VBA set the Security level to low/medium in (Tools|Macro|Security). From workbook launch VBE using short-key Alt+F11. From menu 'Insert' a module and paste the below function. Save. Get back to Workbook. Function GetValue(varRange As Range, intIndent As Integer) GetValue = "" If varRange.IndentLevel = intIndent Then GetValue = varRange End Function Now if the data to be extracted is in ColA; in cell B1 enter the below formula =getvalue(A1,12) 'where 12 represents the indent level. The funciton returns the value if indent level is 12. You can change the indent level to suit your requirement. If this post helps click Yes --------------- Jacob Skaria "dude_down_under_1" wrote: Is there a way to get Excel to return the alignment settings of a cell ? I use the CELL function extensively but it only returns limited information. I have a spreadsheet provided by a client where I need to extract all rows which are left aligned with an indent of 12, but I can't find a function that will return such information to me so that I can do this extract. Any assistance would be appreciated. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
text alignment settings
I'm not sure exactly what you need in the end (cells, multiple cells in a
row, the entire row, something else), but perhaps this non-VB procedure might be of use (if you really need this in VB, the procedure can be coded). Click Edit/Find on Excel's menu bar, click the "Options" button to display the full options for the dialog box (unless the options are already displayed, of course), leave the "Find what" field blank (empty, with nothing in it) and click the Format button. Click the "Alignment" tab and select "Left (Indent)" from the Horizontal drop down and put 12 in the Indent field and then click the OK button. Now, click the "Find All" button and then key in Ctrl+A.... doing this will select every cell on the worksheet with that particular format. -- Rick (MVP - Excel) "dude_down_under_1" wrote in message ... Is there a way to get Excel to return the alignment settings of a cell ? I use the CELL function extensively but it only returns limited information. I have a spreadsheet provided by a client where I need to extract all rows which are left aligned with an indent of 12, but I can't find a function that will return such information to me so that I can do this extract. Any assistance would be appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
In Cell alignment, how do I update the default vertical alignment | Setting up and Configuration of Excel | |||
Text Alignment | Excel Discussion (Misc queries) | |||
Text alignment | Excel Discussion (Misc queries) | |||
default settings under alignment tab in Excel | Excel Discussion (Misc queries) | |||
Top alignment with vertical text? | Excel Discussion (Misc queries) |