+ Reply to Thread
Results 1 to 8 of 8

Extract text from a cell without the last string after some character.

  1. 03-20-2020, 04:14 PM #1
    tomasrage
    tomasrage is offline
    Registered User
    Join Date
    11-27-2018
    Location
    Mexico
    MS-Off Ver
    2019
    Posts
    13

    Extract text from a cell without the last string after some character.

    Hi Guys, I have this Text inside of "A1" cell "Josh.Strap.12" "A2" cell "Rina.Gomz.123" "A3" cell "Gonz.Gre.1234"

    I need to get in the "B" cell the name without the characters after the last dot "." character
    IE.
    Cell A1 Josh.Strap.12 Cell B1 Josh.Strap
    Cell A2 Rina.Gomz.123 Cell B2 Rina.Gomz
    Cell A3 Gonz.Gre.1234 Cell.B3 Gonz.Gre

    Any idea?
    Reply With QuoteRegister To Reply
  2. 03-20-2020, 04:23 PM #2
    Fluff13
    Fluff13 is offline
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,077

    Re: Extract text from a cell without the last string after some character.

    How about
    =TRIM(LEFT(SUBSTITUTE(A1,".",REPT(" ",100),2),100))
    Reply With QuoteRegister To Reply
  3. 03-20-2020, 05:55 PM #3
    tomasrage
    tomasrage is offline
    Registered User
    Join Date
    11-27-2018
    Location
    Mexico
    MS-Off Ver
    2019
    Posts
    13

    Re: Extract text from a cell without the last string after some character.

    Great!!
    Thank you!!
    Reply With QuoteRegister To Reply
  4. 03-20-2020, 07:49 PM #4
    Ghozi Alkatiri
    Ghozi Alkatiri is offline
    Valued Forum Contributor
    Join Date
    10-29-2012
    Location
    Mojokerto,Indonesia
    MS-Off Ver
    Excel 2007
    Posts
    554

    Re: Extract text from a cell without the last string after some character.

    or the other way
    array formula
    =LEFT(A1,MAX(IF(MID(A1,ROW(1:100),1)=".",COLUMNS(A:DZ)))-1)
    Reply With QuoteRegister To Reply
  5. 03-21-2020, 09:50 AM #5
    Fluff13
    Fluff13 is offline
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,077

    Re: Extract text from a cell without the last string after some character.

    Quote Originally Posted by tomasrage View Post
    Great!!
    Thank you!!
    You're welcome & thanks for the feedback
    Reply With QuoteRegister To Reply
  6. 03-21-2020, 10:05 AM #6
    PCI
    PCI is offline
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,066

    Re: Extract text from a cell without the last string after some character.

    Why not
    =LEFT(A1,FIND("#",SUBSTITUTE(A1,".","#",2))-1)
    - Battle without fear gives no glory - Just try
    Reply With QuoteRegister To Reply
  7. 05-25-2020, 01:55 AM #7
    tomasrage
    tomasrage is offline
    Registered User
    Join Date
    11-27-2018
    Location
    Mexico
    MS-Off Ver
    2019
    Posts
    13

    Re: Extract text from a cell without the last string after some character.

    Hello guys, I need this formula in VBA, I tried to record the macro and see the code but Is not working since the data is not in a cell, I need to fill the cell with the data from the filename.
    Any way to convert this formula 
    Please Login or Register  to view this content.
    in VBA code?
    Reply With QuoteRegister To Reply
  8. 05-25-2020, 04:19 AM #8
    sintek
    sintek is offline
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,210

    Re: Extract text from a cell without the last string after some character.

    No need for conversion...Just change formula to values...
    Please Login or Register  to view this content.
    Good Luck
    I don't presume to know what I am doing, however, just like you, I too started somewhere...
    One-day, One-problem at a time!!!
    If you feel I have helped, please click on the star to left of post [Add Reputation]
    Also....add a comment if you like!!!!
    And remember...Mark Thread as Solved.
    Excel Forum Rocks!!!
    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 all red coloured text in a cell up until the first black character
    By jonscrut in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-11-2014, 05:47 AM
  2. [SOLVED] How to extract a varying character-length piece of text from the center of cell's text
    By RuthK in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-13-2014, 11:03 PM
  3. Extract text after the LAST special character in a text string
    By phrankndonna in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-21-2013, 11:16 AM
  4. [SOLVED] extract string after nth string (special character)
    By itselflearn in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-24-2013, 03:18 PM
  5. [SOLVED] Highlight row based on character/text string containing in cell.
    By dihris in forum Excel General
    Replies: 2
    Last Post: 12-03-2012, 11:16 AM
  6. Separate text string so each character is in a different cell
    By MattVarnell in forum Excel General
    Replies: 4
    Last Post: 01-17-2011, 11:44 PM
  7. Extract the nth character in a string of text
    By Chopin in forum Excel General
    Replies: 2
    Last Post: 10-10-2006, 07:05 AM

Tags for this Thread

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