#1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 46
Default format string

I have a string imported from a ".csv file (phone number) as follows. "=1
123-456-7890).

I need to end up with a string formated as a # as followes (11234567890).
Can someone help this old man with a fancy hi-tech way of doen this. :-(

thanks
--
rustyrunrite
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8,520
Default format string

In cell A1
1123-456-7890

In cell B1 enter formula
=--SUBSTITUTE(A1,"-",)

If this post helps click Yes
---------------
Jacob Skaria


"Rusty" wrote:

I have a string imported from a ".csv file (phone number) as follows. "=1
123-456-7890).

I need to end up with a string formated as a # as followes (11234567890).
Can someone help this old man with a fancy hi-tech way of doen this. :-(

thanks
--
rustyrunrite

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default format string

If you really imported the data as formulas, then you may need to select that
range and do:
Edit|Copy
followed by
Edit|paste special|Values

After that, you could select the range to fix (a single column???)

And do multiple edit|replace's
One to replace the hyphens with nothing
one to replace the ('s with nothing
one to replace the )'s with nothing
(Repeat for any other character you want to remove)

After you've removed all the "invalid" characters, you could format the cells:
Format|cells|number tab
Custom category:
type:
(00000000000)
(or whatever format you like)

Rusty wrote:

I have a string imported from a ".csv file (phone number) as follows. "=1
123-456-7890).

I need to end up with a string formated as a # as followes (11234567890).
Can someone help this old man with a fancy hi-tech way of doen this. :-(

thanks
--
rustyrunrite


--

Dave Peterson
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
Converting a time format to a String format in Excel Bill Partridge Excel Discussion (Misc queries) 3 October 3rd 07 11:48 AM
Format text string Saintsman Excel Discussion (Misc queries) 2 April 2nd 07 11:56 AM
Reverse and format string Mike Excel Discussion (Misc queries) 3 October 31st 06 08:21 PM
Format amount in string [email protected] Excel Discussion (Misc queries) 2 October 13th 06 02:46 PM
Date format in string Adam Thwaites Excel Discussion (Misc queries) 3 May 10th 06 04:14 PM


All times are GMT +1. The time now is 01:00 PM.

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

About Us

"It's about Microsoft Excel"