Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 71
Default Formatting SS#'s without hyphens

Is there any way to format a column of Social Security numbers without
hyphens? I've tried a custom format but that isn't working. Am I missing
something? Thanks for your help.
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,173
Default Formatting SS#'s without hyphens

Anita

What do you have and what should a ss# look like. Format in Excel are not
like placeholders in Access so you will likely need a temporary helper
column while you add hyphens

pb

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"Anita" wrote in message
...
Is there any way to format a column of Social Security numbers without
hyphens? I've tried a custom format but that isn't working. Am I missing
something? Thanks for your help.


  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 71
Default Formatting SS#'s without hyphens

We are bringing in a basic ss# with hyphens 000-00-0000 and we need to use
them to apply to a spreadsheet but without the hyphens i.e. 000000000. We'd
like to format the entire column before importing to the spreadsheet. Thanks.
I hope that's clearer.

"Nick Hodge" wrote:

Anita

What do you have and what should a ss# look like. Format in Excel are not
like placeholders in Access so you will likely need a temporary helper
column while you add hyphens

pb

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"Anita" wrote in message
...
Is there any way to format a column of Social Security numbers without
hyphens? I've tried a custom format but that isn't working. Am I missing
something? Thanks for your help.


  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,173
Default Formatting SS#'s without hyphens

Anita

Not sure I do, but it sound like you want to remove the hyphens. If the SS#
are in column A in B1 enter

=TEXT(LEFT(A1,3)&MID(A1,5,2)&RIGHT(A1,4))

and copy down

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"Anita" wrote in message
...
We are bringing in a basic ss# with hyphens 000-00-0000 and we need to use
them to apply to a spreadsheet but without the hyphens i.e. 000000000.
We'd
like to format the entire column before importing to the spreadsheet.
Thanks.
I hope that's clearer.

"Nick Hodge" wrote:

Anita

What do you have and what should a ss# look like. Format in Excel are not
like placeholders in Access so you will likely need a temporary helper
column while you add hyphens

pb

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"Anita" wrote in message
...
Is there any way to format a column of Social Security numbers without
hyphens? I've tried a custom format but that isn't working. Am I
missing
something? Thanks for your help.



  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 71
Default Formatting SS#'s without hyphens

Yes, I do want to remove the hyphens....thank you. I'll try that.

Anita

"Nick Hodge" wrote:

Anita

Not sure I do, but it sound like you want to remove the hyphens. If the SS#
are in column A in B1 enter

=TEXT(LEFT(A1,3)&MID(A1,5,2)&RIGHT(A1,4))

and copy down

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"Anita" wrote in message
...
We are bringing in a basic ss# with hyphens 000-00-0000 and we need to use
them to apply to a spreadsheet but without the hyphens i.e. 000000000.
We'd
like to format the entire column before importing to the spreadsheet.
Thanks.
I hope that's clearer.

"Nick Hodge" wrote:

Anita

What do you have and what should a ss# look like. Format in Excel are not
like placeholders in Access so you will likely need a temporary helper
column while you add hyphens

pb

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"Anita" wrote in message
...
Is there any way to format a column of Social Security numbers without
hyphens? I've tried a custom format but that isn't working. Am I
missing
something? Thanks for your help.




  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5
Default Formatting SS#'s without hyphens

I'm also having the same issue as Anita - but I don't understand the
directions you cite: =TEXT(LEFT(A1,3)&MID(A1,5,2)&RIGHT(A1,4))

Are you saying put 000-11-1234 in A1 and then this formula in B1?

Is there another step after adding the formula you cite?


"Nick Hodge" wrote:

Anita

Not sure I do, but it sound like you want to remove the hyphens. If the SS#
are in column A in B1 enter

=TEXT(LEFT(A1,3)&MID(A1,5,2)&RIGHT(A1,4))

and copy down

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"Anita" wrote in message
...
We are bringing in a basic ss# with hyphens 000-00-0000 and we need to use
them to apply to a spreadsheet but without the hyphens i.e. 000000000.
We'd
like to format the entire column before importing to the spreadsheet.
Thanks.
I hope that's clearer.

"Nick Hodge" wrote:

Anita

What do you have and what should a ss# look like. Format in Excel are not
like placeholders in Access so you will likely need a temporary helper
column while you add hyphens

pb

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"Anita" wrote in message
...
Is there any way to format a column of Social Security numbers without
hyphens? I've tried a custom format but that isn't working. Am I
missing
something? Thanks for your help.


  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,572
Default Formatting SS#'s without hyphens

*After* importing, why not try:
<Edit <Replace
And replace the dashes with nothing?
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Anita" wrote in message
...
We are bringing in a basic ss# with hyphens 000-00-0000 and we need to use
them to apply to a spreadsheet but without the hyphens i.e. 000000000.
We'd
like to format the entire column before importing to the spreadsheet.
Thanks.
I hope that's clearer.

"Nick Hodge" wrote:

Anita

What do you have and what should a ss# look like. Format in Excel are not
like placeholders in Access so you will likely need a temporary helper
column while you add hyphens

pb

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"Anita" wrote in message
...
Is there any way to format a column of Social Security numbers without
hyphens? I've tried a custom format but that isn't working. Am I
missing
something? Thanks for your help.



  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 71
Default Formatting SS#'s without hyphens

Because I would have to do each number individually, wouldn't I? That's not
an option with as many as we may be bringing in. Thanks, RD

"RagDyer" wrote:

*After* importing, why not try:
<Edit <Replace
And replace the dashes with nothing?
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Anita" wrote in message
...
We are bringing in a basic ss# with hyphens 000-00-0000 and we need to use
them to apply to a spreadsheet but without the hyphens i.e. 000000000.
We'd
like to format the entire column before importing to the spreadsheet.
Thanks.
I hope that's clearer.

"Nick Hodge" wrote:

Anita

What do you have and what should a ss# look like. Format in Excel are not
like placeholders in Access so you will likely need a temporary helper
column while you add hyphens

pb

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"Anita" wrote in message
...
Is there any way to format a column of Social Security numbers without
hyphens? I've tried a custom format but that isn't working. Am I
missing
something? Thanks for your help.



  #9   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 422
Default Formatting SS#'s without hyphens

The Edit Replace can work on a range of cells <<all at once
Highlight or Select the range then use the Replace what: "-"
Replace With: ""
Click the "Replace All" Button

HTH

"Anita" wrote in message
:

Because I would have to do each number individually, wouldn't I? That's not
an option with as many as we may be bringing in. Thanks, RD

"RagDyer" wrote:

*After* importing, why not try:
<Edit <Replace
And replace the dashes with nothing?
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Anita" wrote in message
...
We are bringing in a basic ss# with hyphens 000-00-0000 and we need to use
them to apply to a spreadsheet but without the hyphens i.e. 000000000.
We'd
like to format the entire column before importing to the spreadsheet.
Thanks.
I hope that's clearer.

"Nick Hodge" wrote:

Anita

What do you have and what should a ss# look like. Format in Excel are not
like placeholders in Access so you will likely need a temporary helper
column while you add hyphens

pb

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"Anita" wrote in message
...
Is there any way to format a column of Social Security numbers without
hyphens? I've tried a custom format but that isn't working. Am I
missing
something? Thanks for your help.




  #10   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,572
Default Formatting SS#'s without hyphens

Absolutely *NOT*!

You can "edit & replace" the *entire* sheet in one shot ... if you wish!
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Anita" wrote in message
...
Because I would have to do each number individually, wouldn't I? That's
not
an option with as many as we may be bringing in. Thanks, RD

"RagDyer" wrote:

*After* importing, why not try:
<Edit <Replace
And replace the dashes with nothing?
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Anita" wrote in message
...
We are bringing in a basic ss# with hyphens 000-00-0000 and we need to
use
them to apply to a spreadsheet but without the hyphens i.e. 000000000.
We'd
like to format the entire column before importing to the spreadsheet.
Thanks.
I hope that's clearer.

"Nick Hodge" wrote:

Anita

What do you have and what should a ss# look like. Format in Excel are
not
like placeholders in Access so you will likely need a temporary helper
column while you add hyphens

pb

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"Anita" wrote in message
...
Is there any way to format a column of Social Security numbers
without
hyphens? I've tried a custom format but that isn't working. Am I
missing
something? Thanks for your help.






Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Deferring conditional formatting? Pheasant Plucker® Excel Discussion (Misc queries) 14 March 17th 06 08:17 PM
Conditional Formatting: "handwritten" circles? UWHusky Excel Discussion (Misc queries) 0 February 25th 06 12:27 AM
Paste Link - retaining formatting Suzanne Marie Excel Discussion (Misc queries) 1 August 18th 05 02:02 AM
conditional formatting with formula mwc0914 Excel Worksheet Functions 2 July 20th 05 08:11 PM
Conditional formatting not available in Excel BAB Excel Discussion (Misc queries) 2 January 1st 05 03:33 PM


All times are GMT +1. The time now is 03:49 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"