+ Reply to Thread
Results 1 to 4 of 4

Extract certain text dependant on number of characters in text

  1. #1
    Registered User
    Join Date
    11-28-2014
    Location
    Birmingham, England
    MS-Off Ver
    Windows 7
    Posts
    25

    Extract certain text dependant on number of characters in text

    Hi All,

    New to posting on here so here's my first query;

    I have a column of data that is made up of alpha and numeric values, I need to extract either the first 3 charcters if the Character length is 7 characters long, or the first 4 characters if it is 8 characters in length into Column B.
    Eg;

    Column A contains the folowing;
    T03HD0A (From this I need T03 in column B)
    T03EF0A (From this I need T03 in column B)
    T01GA0A (From this I need T01 in column B)
    XW2AAA0A (From this I need XW2A in column B)
    XW2BAA0A (From this I need XW2B in column B1)

    I have tried LEFT and IF but can't seem to ocmbine tHE two to get the result I want.

    Any help gratefully received.

  2. #2
    Forum Expert azumi's Avatar
    Join Date
    12-10-2012
    Location
    YK, Indonesia
    MS-Off Ver
    Excel 2019
    Posts
    2,368

    Re: Extract certain text dependant on number of characters in text

    =IF(LEN(A1)=7,LEFT(A1,3),LEFT(A1,4))

    put in B1 and dragged down

  3. #3
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,770

    Re: Extract certain text dependant on number of characters in text

    In B1 Cell

    =IFERROR(LEFT(A1,LEN(A1)-4),"")


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  4. #4
    Registered User
    Join Date
    11-28-2014
    Location
    Birmingham, England
    MS-Off Ver
    Windows 7
    Posts
    25

    Re: Extract certain text dependant on number of characters in text

    Many thanks Azumi and Sixthsense for your quick replies both have solved my problem!

+ 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. Extract text between two characters in a string - varing text length
    By luv2birdie in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-04-2014, 06:10 PM
  2. [SOLVED] Extract 50 characters text to a new worksheet
    By skyflare in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-02-2013, 01:44 AM
  3. [SOLVED] Extract Specific Data from range dependant on text criteria
    By Grimace in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 07-09-2013, 08:43 PM
  4. Extract Text characters only from a cell
    By judasdac in forum Excel General
    Replies: 2
    Last Post: 05-06-2009, 12:22 PM
  5. extract text separated by characters
    By RANDYC1152 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 04-25-2009, 09:54 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1