Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Worksheets are stuck on default text format with left align
This is really making me crazy.
I have data that I have downloaded from mysql. The problem I am having is that every cell is defaulting to text format with a left align (the field starts out with a hidden apostrophe). No big deal except that I need to convert on of the text fields by inserting a dash in between words so that long names will be ready to concatenate into web addresses. The following steps have not worked Changing the cell format to general - it says general but the dang ' is still there Copying the format of a blank cell and using the format painter Copying a blank cell and pasting the format (remember every field defaults to test) Applying various Trim, Clean, etc functions. I have saved it to numerous format including xls and cvs. I have copied to word and can not replace I have imported to access and tried something there - no luck I am thinking that there is some universal setting that needs to be changed. Any ideas? Thank you! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Worksheets are stuck on default text format with left align
Try running this macro after importing the data and see if it doesn't help.
Some things could take on a different appearance, for example a long number (as a SSAN or phone number without dashes) could take on scientific notation if it's in a relatively narrow column. Open a copy of a workbook with data in it and press [Alt]+[F11] to open the VB Editor, in it choose Insert -- Module and then copy the code below and paste it into the code module. Close the VB Editor and select the worksheet with the data and run the macro, either from tools -- Macro -- Macros (pre XL2007, or from the developer tab in XL 2007). Sub RemoveLeadingMarker() Dim allCells As Range Dim anyCell As Range Set allCells = ActiveSheet.UsedRange Application.ScreenUpdating = False For Each anyCell In allCells If Not IsEmpty(anyCell) Then anyCell.Value = anyCell.Value End If Next Set allCells = Nothing MsgBox "Task completed" End Sub "xineyeager" wrote: This is really making me crazy. I have data that I have downloaded from mysql. The problem I am having is that every cell is defaulting to text format with a left align (the field starts out with a hidden apostrophe). No big deal except that I need to convert on of the text fields by inserting a dash in between words so that long names will be ready to concatenate into web addresses. The following steps have not worked Changing the cell format to general - it says general but the dang ' is still there Copying the format of a blank cell and using the format painter Copying a blank cell and pasting the format (remember every field defaults to test) Applying various Trim, Clean, etc functions. I have saved it to numerous format including xls and cvs. I have copied to word and can not replace I have imported to access and tried something there - no luck I am thinking that there is some universal setting that needs to be changed. Any ideas? Thank you! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Worksheets are stuck on default text format with left align
Hi,
I think the key here is the leading apostrophe which will keep the cell formatted as text no matter what physical format you select and we need to get rid of those. Select your data then run this macro and you should be rid of the pests Sub Sonic() For Each c In Selection If Not c.HasFormula Then c.Value = c.Value Next End Sub -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "xineyeager" wrote: This is really making me crazy. I have data that I have downloaded from mysql. The problem I am having is that every cell is defaulting to text format with a left align (the field starts out with a hidden apostrophe). No big deal except that I need to convert on of the text fields by inserting a dash in between words so that long names will be ready to concatenate into web addresses. The following steps have not worked Changing the cell format to general - it says general but the dang ' is still there Copying the format of a blank cell and using the format painter Copying a blank cell and pasting the format (remember every field defaults to test) Applying various Trim, Clean, etc functions. I have saved it to numerous format including xls and cvs. I have copied to word and can not replace I have imported to access and tried something there - no luck I am thinking that there is some universal setting that needs to be changed. Any ideas? Thank you! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Align Left and Right in Same Cell | Excel Discussion (Misc queries) | |||
Can I reset the default cell alignment format to Left-Top? | Excel Discussion (Misc queries) | |||
Left Align Right Section of Custom Footer | Setting up and Configuration of Excel | |||
Left align text labels in horizontal bar chart | Charts and Charting in Excel | |||
Left align '$' and right align numbers? | Excel Discussion (Misc queries) |