+ Reply to Thread
Results 1 to 8 of 8

Copying Values of a Cell After a Specific Character

  1. #1
    Registered User
    Join Date
    06-11-2013
    Location
    NYC
    MS-Off Ver
    Excel 2007
    Posts
    42

    Copying Values of a Cell After a Specific Character

    Hi there,

    I have a list of numbers in a column in Excel that I need to copy into another cell using a formula. I know that I could do an easy =A2 formula, etc, but some of the cells with the data that I need copied have one number and others have two numbers from which I need to take the second.

    Here's an example of the values in my column that I need to copy into another column:

    100000000
    150000000
    50000000|19775000
    2000000000
    100000000
    35000000
    50000000

    8000000000
    200000000

    For the cells with more than one number, I need to copy over the number that appears to the right of the "|" character. So essentially, I need a formula that would work regardless of whether the referenced cell contains one number (that number is populated into the new cell), more than one number (the number furthest to the right is copied into the new cell), or are blank (new cell would be blank)>

    Any idea how to do this?

  2. #2
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Copying Values of a Cell After a Specific Character

    Try:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

  3. #3
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Copying Values of a Cell After a Specific Character

    One way: =IFERROR(MID(A1, FIND("|", A1) + 1, 99), A1)
    Entia non sunt multiplicanda sine necessitate

  4. #4
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Copying Values of a Cell After a Specific Character

    Or you could use a small UDF:
    Please Login or Register  to view this content.
    Use as:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Or if you changed the separator, for example, to "#":
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  5. #5
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Copying Values of a Cell After a Specific Character

    Maybe this one

    =TRIM(RIGHT(SUBSTITUTE(A1,"|",REPT(" ",255)),255))

    A
    B
    1
    100000000|6549876546 6549876546
    2
    150000000
    150000000
    3
    50000000|19775000|25657|654789 654789
    4
    2000000000
    2000000000
    5
    100000000
    100000000
    6
    35000000
    35000000
    7
    50000000
    50000000
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  6. #6
    Registered User
    Join Date
    06-11-2013
    Location
    NYC
    MS-Off Ver
    Excel 2007
    Posts
    42

    Re: Copying Values of a Cell After a Specific Character

    These solutions are great! Each of them worked perfectly! Thanks so much!

  7. #7
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Copying Values of a Cell After a Specific Character

    We're waiting for the Rep

  8. #8
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Copying Values of a Cell After a Specific Character

    Thank you for the feedback!

    Please mark thread as "Solved" if your issue has been resolved. (Selecting Thread Tools-> Mark thread as Solved).

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] SUM values only if cell starts with specific character
    By miop in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 09-17-2013, 04:27 PM
  2. Copying over specific values when a specific value is entered.
    By jampy00 in forum Excel Formulas & Functions
    Replies: 30
    Last Post: 12-11-2012, 01:23 PM
  3. Replies: 4
    Last Post: 09-06-2011, 10:36 AM
  4. Copying Text Right of Specific Character
    By LevinTrueno in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 10-10-2008, 03:34 PM
  5. copying cell values to specific area in sheet
    By Reinder in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-12-2007, 09:55 AM

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