Prev: How specify number format of cell value in concatenate function?
Next: Excel's COMBIN and integers
From: xineyeager on 27 Mar 2010 14:25
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!
From: JLatham on 27 Mar 2010 17:33
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!
From: Mike H on 27 Mar 2010 17:34
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!
 | 
Pages: 1
Prev: How specify number format of cell value in concatenate function?
Next: Excel's COMBIN and integers