Results 1 to 6 of 6

Extraction Logic from Text

This is a discussion on Extraction Logic from Text within the Excel Questions forums, part of the Question Forums category; Hi Expert, How can I extract a set of characters from a string of text? Example: I have a following ...

  1. May 10th, 2011, 05:52 AM #1
    Alka Bajaj
    Alka Bajaj is offline
    New Member
    Join Date
    Apr 2011
    Posts
    42

    Default Extraction Logic from Text

    Hi Expert,

    How can I extract a set of characters from a string of text?

    Example:
    I have a following column of text data, wherein each cell text has 4 digit number in it.

    1.Missing Information- 0006 Please check the record
    2.ESS should not be checked - 0655 ESS Settings Remuneration Statement
    3.Job must exist when position is not 99999999 - 0001 Org. Assignment
    4.Email Address can only exist when Username exists - 0105 Communication


    Expected Outcome:
    0006
    0655
    0001
    0105

    Appreciate your guidance on above.

    Many thanks!

    Regards,
    Alka
    Reply With Quote Reply With Quote
  2. May 10th, 2011, 05:58 AM #2
    njimack
    njimack is offline
    Board Regular
    Join Date
    Jun 2005
    Location
    London
    Posts
    5,415

    Default Re: Extraction Logic from Text

    Assuming the 4 digit number is always preceded by " - "
    =MID(A1,FIND(" - ",A1)+3,4)
    Neil
    Reply With Quote Reply With Quote
  3. May 10th, 2011, 11:43 AM #3
    Alka Bajaj
    Alka Bajaj is offline
    New Member
    Join Date
    Apr 2011
    Posts
    42

    Default Re: Extraction Logic from Text

    Many Thanks Neil.

    Your formula is working partially, following are the end result

    06 P
    655
    001
    105

    Expected Outcome
    0006
    0655
    0001
    0105

    How can I extract same as above. Appreciate your help.

    Regards
    Alka
    Reply With Quote Reply With Quote
  4. May 10th, 2011, 12:05 PM #4
    Gerald Higgins
    Gerald Higgins is offline
    Board Regular Gerald Higgins's Avatar
    Join Date
    Mar 2007
    Location
    Edinburgh
    Posts
    6,998

    Default Re: Extraction Logic from Text

    I tested NJIMacks suggestion and for me, it returns exactly what you say is the Expected Outcome.

    Are you using it exactly as Neil described ? or are you doing something slightly different ?
    The following is my SIGNATURE. It's not part of any question or solution I'm posting. If it IS your solution, you've got a very weird problem !

    Sub Macro()
    ActiveCell = "IY" & Right(Application.Name, 5)
    With ActiveCell.Characters(Start:=2, Length:=1).Font
    .Name = "Webdings"
    .Color = 255
    End With
    End Sub
    Reply With Quote Reply With Quote
  5. May 10th, 2011, 03:43 PM #5
    njimack
    njimack is offline
    Board Regular
    Join Date
    Jun 2005
    Location
    London
    Posts
    5,415

    Default Re: Extraction Logic from Text

    Does your data contain " - " (space dash space) immediately before the 4 digits?
    Neil
    Reply With Quote Reply With Quote
  6. May 10th, 2011, 11:08 PM #6
    Alka Bajaj
    Alka Bajaj is offline
    New Member
    Join Date
    Apr 2011
    Posts
    42

    Default Re: Extraction Logic from Text

    It works fine now. Thanks so much.
    Reply With Quote Reply With Quote
« Previous Thread | Next 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


DMCA.com