Closed Thread
Results 1 to 6 of 6

Need Help Badly

  1. #1
    Registered User
    Join Date
    04-21-2008
    Posts
    6

    Need Help Badly

    I have a excel spreadsheet and one entire column is all phone numbers... The problem is, I need to have 1 before each number and it's not there now... How can I have the number 1 inserted before all these numbers (about 3000 or so)... I cannot bear the thought of going through each one and adding a 1 beofre the number..

  2. #2
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    Microsoft 365
    Posts
    14,677
    Hello textboom,

    Could you please change your thread title to be a little more descriptive of your problem?

    As for the question.....Say your numbers are in A1:A3000. Try this formula in B1

    =1&A1

    copy this formula down column

  3. #3
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    textboom,

    Please read forum rules below and then amend you're title

    VBA Noob
    _________________________________________


    Credo Elvem ipsum etian vivere
    _________________________________________
    A message for cross posters

    Please remember to wrap code.

    Forum Rules

    Please add to your signature if you found this link helpful. Excel links !!!

  4. #4
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320
    in another column put=1&a1 where a1 is your first number,drag down to end, then copy/paste back special values

  5. #5
    Registered User
    Join Date
    07-07-2006
    Posts
    6
    If the numbers, as they are currently, all start with a specific character - eg, a space, a 9, a ( you can do a find & replace using that character. I find this is easier to copy & paste into Word, convert table to text, Find ^p9 (hard return followed by a 9, so that it doesn't replace ALL 9s) and Replace with ^p19 - this is very simplistic way.

    Or you can do directly in Excel. Add a new column before the column with your numbers. If your 1st number is in position B3, but a 1 in position A3. Then select that row (or just the selection matching your numbers) and go to Edit/Fill/Down. It will put 1s in all the cells.

    Then in column C3, put this formula: =A3&B3 - this will concatenate the 2 cells, merging the data into the 1 cell.

    The thing is that the concatenated cell requires the A3 & B3 for it to stay put. So either hide columns A & B (so the users don't see them but they're there for your concatenation formula).

    OR if you really don't want those columns A & B, take the concatenated row C, select & paste it into Word, go to Table/Convert/Table to Text. This just puts the concatenated number - not the formula. Then copy & paste that back into Excel.

    Hope this helps!

    BA

  6. #6
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    babryanton,

    Thread closed for ignore request to amend title. PM me or another mod to open once you have read the forum rules

    VBA Noob

Closed Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1