+ Reply to Thread
Results 1 to 15 of 15

Extract Data from Column, which contain character "/" and in Left side of character "-"

  1. 02-27-2021, 09:51 AM #1
    ionelz
    ionelz is offline
    Forum Contributor
    Join Date
    05-20-2016
    Location
    Detroit
    MS-Off Ver
    Office 365 Version 2008 (July 2020)
    Posts
    819

    Extract Data from Column, which contain character "/" and in Left side of character "-"

    Hi,
    First, I had this question here, and was solved !
    But now I got Office 365 Version 2008 (July 2020) and would like to simplify my formulas and columns help

    Data is in B4:B500, something like this :
    Looking ONLY for data which contain character "/" and then extract in left side of "-"

    12345678/001-name1
    text1
    22558877/089-name2
    33445588/012-name3
    text2
    text3
    11992288/004-name3

    Result should be in Column C
    12345678/001
    22558877/089
    33445588/012
    11992288/004

    Thank you
    Reply With QuoteRegister To Reply
  2. 02-27-2021, 10:11 AM #2
    Pepe Le Mokko
    Pepe Le Mokko is offline
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,445

    Re: Extract Data from Column, which contain character "/" and in Left side of character "-

    Administrative note

    Welcome to the forum

    in your haste to solve your problem, you probably missed the yellow banner advising how to get answers faster by posting a sheet ?

    Please take a moment to read it and attach a sheet accordingly.

    Thanks you for helping us help you
    Reply With QuoteRegister To Reply
  3. 02-27-2021, 10:34 AM #3
    Fluff13
    Fluff13 is offline
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,077

    Re: Extract Data from Column, which contain character "/" and in Left side of character "-

    How about
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  4. 02-27-2021, 03:05 PM #4
    Bo_Ry
    Bo_Ry is offline
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Extract Data from Column, which contain character "/" and in Left side of character "-

    Please try
    =LEFT(FILTER(B4:B500,ISNUMBER(SEARCH("/",B4:B500))),12)

    or if "-" is not in the same position
    =TRIM(LEFT(SUBSTITUTE(FILTER(B4:B500,ISNUMBER(SEARCH("/",B4:B500))),"-",REPT(" ",50)),50))
    Linkedin/Bo-Rydobon
    Youtube.com/XcWizard
    Facebook.com/XcWizard
    Reply With QuoteRegister To Reply
  5. 02-27-2021, 09:52 PM #5
    ionelz
    ionelz is offline
    Forum Contributor
    Join Date
    05-20-2016
    Location
    Detroit
    MS-Off Ver
    Office 365 Version 2008 (July 2020)
    Posts
    819

    Re: Extract Data from Column, which contain character "/" and in Left side of character "-

    Thank you all, all formulas work
    However I have one more question on this which was too hard to explain without above answers
    In attached, I need to EXTRACT : in G, H, I

    - Unique from C
    - Sorted by E
    - In I, for Unique, retain LARGEST value
    Attached Files Attached Files
    Last edited by ionelz; 02-27-2021 at 09:55 PM.
    Reply With QuoteRegister To Reply
  6. 02-28-2021, 02:43 AM #6
    Bo_Ry
    Bo_Ry is offline
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Extract Data from Column, which contain character "/" and in Left side of character "-

    Please try at
    G3
    Formula: copy to clipboard 
    Please Login or Register  to view this content.



    H3
    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    I3
    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    or for Update MS365 with LET function

    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  7. 02-28-2021, 09:40 AM #7
    Gregb11
    Gregb11 is offline
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    2,823

    Re: Extract Data from Column, which contain character "/" and in Left side of character "-

    @Bo_Ry
    Your LET formula - THAT. IS. AMAZING!!
    Reply With QuoteRegister To Reply
  8. 02-28-2021, 12:52 PM #8
    ionelz
    ionelz is offline
    Forum Contributor
    Join Date
    05-20-2016
    Location
    Detroit
    MS-Off Ver
    Office 365 Version 2008 (July 2020)
    Posts
    819

    Re: Extract Data from Column, which contain character "/" and in Left side of character "-

    Bo_Ry, thank you so much for your Help.
    However I am looking at your formulas, it take time to think, to understand what I want and develop them...so thank you again
    Please see attached, see J
    All Ok, but please let me say again at once :

    - Extract from B
    - Unique
    - only which contain character "/"
    - in Left side of "." if exist and if no in Left side of "-" (see J,)
    - sorted ascending by Leading spaces of B
    - and in leading spaces Column, retain the Largest value of Unique

    So all Ok
    If you could integrate J in your formulas, ok, if no I have no problem with an additional column
    Thank you so much !
    (Let looks great, I do not have it yet)
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  9. 02-28-2021, 01:39 PM #9
    ionelz
    ionelz is offline
    Forum Contributor
    Join Date
    05-20-2016
    Location
    Detroit
    MS-Off Ver
    Office 365 Version 2008 (July 2020)
    Posts
    819

    Re: Extract Data from Column, which contain character "/" and in Left side of character "-

    or

    - Unique
    - Only with Character "/"
    - LEFT side of "." if exists ELSE Left side of "-" (which exist for sure)

    the rest with leading space and sorting, is the same
    Last edited by ionelz; 02-28-2021 at 05:07 PM.
    Reply With QuoteRegister To Reply
  10. 02-28-2021, 02:33 PM #10
    Fluff13
    Fluff13 is offline
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,077

    Re: Extract Data from Column, which contain character "/" and in Left side of character "-

    How about this mod to Bo_Ry's LET formula
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  11. 02-28-2021, 05:26 PM #11
    ionelz
    ionelz is offline
    Forum Contributor
    Join Date
    05-20-2016
    Location
    Detroit
    MS-Off Ver
    Office 365 Version 2008 (July 2020)
    Posts
    819

    Re: Extract Data from Column, which contain character "/" and in Left side of character "-

    Yes, but Bo_Ry wrote formula as example, I do not have that...
    Reply With QuoteRegister To Reply
  12. 02-28-2021, 05:41 PM #12
    Gregb11
    Gregb11 is offline
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    2,823

    Re: Extract Data from Column, which contain character "/" and in Left side of character "-

    You don't have what? LET? You should. Everyone with 365 should have the LET formula. If you don't, you just need to update.
    Reply With QuoteRegister To Reply
  13. 02-28-2021, 06:26 PM #13
    ionelz
    ionelz is offline
    Forum Contributor
    Join Date
    05-20-2016
    Location
    Detroit
    MS-Off Ver
    Office 365 Version 2008 (July 2020)
    Posts
    819

    Re: Extract Data from Column, which contain character "/" and in Left side of character "-

    You right, I will update...
    Reply With QuoteRegister To Reply
  14. 02-28-2021, 06:42 PM #14
    ionelz
    ionelz is offline
    Forum Contributor
    Join Date
    05-20-2016
    Location
    Detroit
    MS-Off Ver
    Office 365 Version 2008 (July 2020)
    Posts
    819

    Re: Extract Data from Column, which contain character "/" and in Left side of character "-

    Found, yes, the LET formula work !
    Thank you all so much !
    Reply With QuoteRegister To Reply
  15. 03-01-2021, 10:04 AM #15
    Fluff13
    Fluff13 is offline
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,077

    Re: Extract Data from Column, which contain character "/" and in Left side of character "-

    Glad to help & thanks for the feedback.
    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] Extract Data from Column, which contain character "/" and in Left side of character "-"
    By ionelz in forum Excel General
    Replies: 14
    Last Post: 05-11-2020, 07:47 AM
  2. [SOLVED] Column X-Ref list - Sheet1 Col A "pages", Col B:FL "Req" to Sheet2 ColA "req", ColB "page"
    By excel-card-pulled in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 04-07-2017, 09:30 AM
  3. "Countif" with "left" error, "Sumproduct" picking up not qualifying rows
    By drgkt in forum Excel General
    Replies: 35
    Last Post: 01-13-2016, 02:16 AM
  4. Remove the first and last group in the pivot field that starts with character ">" and "<"
    By anarieadea in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-10-2014, 01:22 PM
  5. [SOLVED] Trim left, but only if the first 3 character start with "The"
    By firstsgt1978 in forum Excel General
    Replies: 7
    Last Post: 03-25-2014, 11:47 AM
  6. [SOLVED] how to replace the "Alt-Enter" character in a cell with sway "@"
    By jmac1947 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 03-04-2014, 05:55 AM
  7. HELP on "left","right","find","len","substitute" functions
    By serene83 in forum Excel General
    Replies: 5
    Last Post: 06-26-2006, 09:23 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