Formatting in Excel

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Darrel - 20 May 2008 03:57 GMT

I have imported a database and cannot format the phone numbers I have tried
formatting the cells prior to importing and also tried conditional
formatting. For some reason these numbers always show up without a space
between the brackets. Can anyone help?

Reply to this Message

Fred Smith - 20 May 2008 06:28 GMT

< Can anyone help? > Yes.

Just tell us what the data looks like, and what you want it to be.

Regards
Fred.

>I have imported a database and cannot format the phone numbers I have tried
> formatting the cells prior to importing and also tried conditional
> formatting. For some reason these numbers always show up without a space
> between the brackets. Can anyone help?

Reply to this Message

Dave Peterson - 20 May 2008 12:42 GMT

It sounds like that the data is text--not real numbers.

Try a few functions that'll describe that cell (before you retype the value).

=istext(a1)
=isnumber(a1)
=len(a1)

You could be pasting extra characters into that cell--maybe extra spaces or
those HTML non-breaking spaces (char(160)'s).

Depending on what you find out, ...

David McRitchie has a macro that can help clean this:
http://www.mvps.org/dmcritchie/excel/join.htm#trimall
(look for "Sub Trimall()")

And if you're new to macros, you may want to read David's intro:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

> I have imported a database and cannot format the phone numbers I have tried
> formatting the cells prior to importing and also tried conditional
> formatting. For some reason these numbers always show up without a space
> between the brackets. Can anyone help?

Signature

Dave Peterson

Reply to this Message