+ Reply to Thread
Results 1 to 8 of 8

Need to remove random numbers and text from the left from each row

Hybrid View

  1. 01-02-2023, 07:52 PM #1
    LilyN
    LilyN is offline
    Registered User
    Join Date
    07-22-2021
    Location
    US
    MS-Off Ver
    Mac Version 16.58
    Posts
    21

    Need to remove random numbers and text from the left from each row

    Hi,

    Can someone help to remove numbers and a text from the left from each row?

    Currently, i have

    [1234567] has associated data:[94857382,193857639]

    I need to have the following:

    [94857382,193857639]

    Thanks,
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 01-02-2023, 07:53 PM #2
    sandy666
    sandy666 is offline
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Need to remove random numbers and text from the left from each row

    with Power Query

    Currently
    [18238576,248375965]
    [11738495,249238575]
    [764548304,198378475,295767604,104956864]
    [183765663,1857569403] 


    // Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ETAD = Table.TransformColumns(Source, {{"Currently", each Text.AfterDelimiter(_, ":"), type text}})
in
    ETAD
    EDIT
    I noticed this is a MacOS so it may not work on this version 16.58
    Last edited by sandy666; 01-03-2023 at 06:46 AM.
    Reply With QuoteRegister To Reply
  3. 01-02-2023, 08:00 PM #3
    TMS
    TMS is online now
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,197

    Re: Need to remove random numbers and text from the left from each row

    Old way:
    Formula: copy to clipboard 
    =IFERROR(RIGHT(A2,LEN(A2)-FIND(":",A2)),"")


    New way:
    Formula: copy to clipboard 
    =TEXTAFTER(A2,":")
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke

    Reply With QuoteRegister To Reply
  4. 01-02-2023, 08:20 PM #4
    Phuocam
    Phuocam is offline
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2024
    Posts
    6,175

    Re: Need to remove random numbers and text from the left from each row

    Or try

    =REPLACE(A2,1,FIND(":",A2&":"),"")
    Reply With QuoteRegister To Reply
  5. 01-03-2023, 06:34 AM #5
    tanasedn
    tanasedn is offline
    Valued Forum Contributor
    Join Date
    06-23-2021
    Location
    Romania
    MS-Off Ver
    2021, 365 v 2208
    Posts
    722

    Re: Need to remove random numbers and text from the left from each row

    Or this:
    Formula: copy to clipboard 
    =MID(A2,FIND(":",A2)+1,1000)
    Diana Tanase

    If the solutions offered helped you to solve your problem, then mark the thread as SOLVED (thread tools in the top menu) and you can click on * to add reputation to those who helped you, as a way to say thank you !
    Reply With QuoteRegister To Reply
  6. 01-03-2023, 07:54 AM #6
    TMS
    TMS is online now
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,197

    Re: Need to remove random numbers and text from the left from each row

    @LilyN: you have a variety of formula solutions plus the Power Query option:

    Formula: copy to clipboard 
    =IFERROR(TEXTAFTER(A2,":"),"")

    Formula: copy to clipboard 
    =REPLACE(A2,1,FIND(":",A2&":"),"")

    Formula: copy to clipboard 
    =IFERROR(MID(A2,FIND(":",A2)+1,1000),"")

    Formula: copy to clipboard 
    =IFERROR(MID(A2,FIND(":",A2)+1,LEN(A2)),"")

    Formula: copy to clipboard 
    =IFERROR(RIGHT(A2,LEN(A2)-FIND(":",A2)),"")


    Maybe time to test and pick one.



    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.
    Reply With QuoteRegister To Reply
  7. 01-03-2023, 03:34 PM #7
    TMS
    TMS is online now
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,197

    Re: Need to remove random numbers and text from the left from each row

    @LilyN: Thanks for the rep.
    Reply With QuoteRegister To Reply
  8. 01-03-2023, 03:54 PM #8
    sandy666
    sandy666 is offline
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Need to remove random numbers and text from the left from each row

    You are welcome and thanks
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Remove before underscore Text from left or right in excel
    By druva in forum Excel General
    Replies: 9
    Last Post: 08-02-2019, 02:52 AM
  2. How to remove space on left of text?I can’t backspace it.
    By Excelforum*ser_mH7 in forum Excel General
    Replies: 11
    Last Post: 05-30-2018, 12:34 PM
  3. [SOLVED] Help! Remove numbers and all chars to left of the numbers.
    By trouble085 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-08-2012, 09:54 PM
  4. remove random spaces in a text string
    By mariusescu in forum Excel General
    Replies: 2
    Last Post: 07-10-2008, 03:32 PM
  5. Remove left text string
    By Luc in Oxford in forum Excel General
    Replies: 2
    Last Post: 08-23-2005, 06:05 AM
  6. [SOLVED] Create a formula to remove text to the left of specified char?
    By TheLazer in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-18-2005, 07:06 AM
  7. How do I remove all text to the left of the @ in an email address
    By David M in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-01-2005, 04:06 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1