Extract text after specific char in a string

[WmK]

HI
Probably simple I just cant figure it out.

I have cell content of a variable length string and I need to extract all text after "///" an example is below:

897/C///Amarnath Leena CD20110316 09105348

I need to pull everything after the "///" Another example would be: 744/I/373632///SUVARNA, VINOD K MD=20110926 231597 Again I need to pull everything after the "///" Right, Left, and Mid wont work since the position is different any thoughts? <tbody> </tbody>

<tbody>
</tbody>

 

[supers0nic]

Reviving and old one!

Here's my similar dilemma. I have this ROUGH data set. Here's a mock example...

The quick ABC 133333339 jumped over the lazy moon 654321
The quick brown fox jumped bc #177777779over the lazy moon
ABC # 124678359 654321 The quick brown fox jumped over the lazy
The quick brown fox jumped over the lazy moonabc # 100050009
The abc number is 987654321 over moon from 4501 XYZ#12345


I would need to extract the 9 digit number after ABC. Here's the kicker, "ABC" can appear in multiple formats.

Example
ABCACB
BAC
Abc
AbC#
ABC #
abc #
bc #
abc number is


Is there a way to match against this list then output the 9 digit number that follows it? Or at least find the text string from that list then output x-amount of characters after finding it?


Help?

 

[hammerhead13]

I have a Similar Issue but Im trying to pull Data using a VLookup

I tried

=IF(ISBLANK(A6)," ",RIGHT(VLOOKUP(A6,'srboard (1)'!$A$2:$X$500,8,FALSE),19)) but it pulls incorrect info if the data varies in Size.

=IF(ISBLANK(A2)," ",MID(VLOOKUP(A2,'srboard (1)'!$A$2:$X$500,8,FALSE),82,25)) For Example when the Serial Number is missing it Pulls too much and its not Accurate.

I want to pull all the Text After "Location:" So no Matter how much data is after Location: It is Returned.

[EXTERNAL] [Support] Incident I8KE143A routed to Ted SN: MXL61PNH Location: Pittsburgh

<tbody>
</tbody>

Thanks in Advance!

 

[jtakw]

Hi,

You have a Space ( " " ) as result when A2 is Blank, you should use a "" Blank instead, does this help:

Book1
	A	B	C
1	[EXTERNAL] [Support] Incident I8KE143A routed to Ted SN: MXL61PNH Location: Pittsburgh	Pittsburgh	#N/A
Sheet207

Cell Formulas
Range	Formula
B1	=MID(A1,SEARCH("Location:",A1)+10,255)
C1	=IF(A2="","",MID(VLOOKUP(A2,'srboard (1)'!$A$2:$X$500,8,FALSE),SEARCH("Location:",A1)+10,255))

B1 formula extracts Everything after "Location:"
C1 formula adapts B1 formula within your VLOOKUP.

 

[jtakw]

Thank you for feedback.

 

[ajt8888]

Ok I have read this and had a few goes but cannot make it work.

In the sample below I need the formula to find FGB and return the 43 characters after that

2016-12-15 10:59:13.251: Resource Change : fer : Nass4-PC:579516 : 15/12/16 10:59 Resource Change Status Change - Resource: FGBS024. Old Status: AT AV. New Status : OI NA Resource Change fer Nass4-PC

<tbody>
</tbody>

Thanks in advance

 

[Fluff]

How about
=MID(A2,SEARCH("FGB",A2&"FGB")+3,43)
or if you want the FGB returned as well
=MID(A2,SEARCH("FGB",A2&"FGB"),46)

 

[ajt8888]

How about
=MID(A2,SEARCH("FGB",A2&"FGB")+3,43)
or if you want the FGB returned as well
=MID(A2,SEARCH("FGB",A2&"FGB"),46)

Wow so simple many thanks

 

[Fluff]

You're welcome & thanks for the feedback

 

[Trouttrap2]

I took this from here: https://www.excelforum.com/excel-general/1167986-locate-9-digit-number-only-in-cell-string.html Seems to answer your question.

Book1
	B	C
1	Text String	Output
2	The quick ABC 133333339 jumped over the lazy moon 654321	133333339
3	The quick brown fox jumped bc [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=177777779over]#177777779over[/URL] the lazy moon	177777779
4	ABC # 124678359 654321 The quick brown fox jumped over the lazy	124678359
5	The quick brown fox jumped over the lazy moonabc # 100050009	100050009
6	The abc number is 987654321 over moon from 4501 XYZ#12345	987654321
Sheet1

Cell Formulas
Range	Formula
C2	{=IF(COUNT(MID(SUBSTITUTE(B2," ","a"),ROW(INDIRECT("1:"&LEN(B2)-8)),9)/ISERROR(--MID(" "&B2,ROW(INDIRECT("1:"&LEN(B2)-8)),1))/ISERROR(--MID(B2&" ",ROW(INDIRECT("10:"&LEN(B2)+1)),1)))>1,"PLEASE CHECK ACC NUMBER",IFERROR(LOOKUP(10^10,MID(SUBSTITUTE(B2," ","a"),ROW(INDIRECT("1:"&LEN(B2)-8)),9)/ISERROR(--MID(" "&B2,ROW(INDIRECT("1:"&LEN(B2)-8)),1))/ISERROR(--MID(B2&" ",ROW(INDIRECT("10:"&LEN(B2)+1)),1))),""))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Fluff]

Are you aware that the question you have answered was asked over 2 years ago?