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>

 

[camle]

I guess I am confused then. If you would be able to "add the different variation as I come about", then what is it about the code I posted in Message #57 that you are unable to do that with it now?

OK sorry for the confusion, now it gives:
ex.
Castle (2009) 6x10 The Good, the Bad & the Baby

"The Good, the Bad & the Baby"

rather than "Castle (2009)"

 

[Rick Rothstein]

OK sorry for the confusion, now it gives:
ex.
Castle (2009) 6x10 The Good, the Bad & the Baby

"The Good, the Bad & the Baby"

rather than "Castle (2009)"

The function you quoted was designed to split apart the data you posted in Message #8 at the position you requested... it is not universally adaptable for just any other purpose. For example, while that function required 11 lines of active code to do what it was designed for, the UDF for your current request requires a lot less code... just one line to be exact...

Function EpisodeName(DataLine As String) As String
  EpisodeName = Split(DataLine, ")")(0) & ")"
End Function

With that said, it would be more efficient to use the formula I posted in Message #58 than to use the above one-liner UDF.

 

[camle]

Thank you my friend

 

[JDM09]

Hi guys, Can someone help me with this Cell Extracting formula. I have cells that contain a persons first and last name along with some characters I would like to omit. Like this: John Doe;#1234 And I also have cells with more than one name like this: John Doe;#1234;#John Doe;#1234. Trying to come up with a formula that just displays the name and ignores the semicolon, pound sign, and numbers. Thanks in advance to anyone that can come up with a effective formula for both scenarios.

 

[Rick Rothstein]

Hi guys, Can someone help me with this Cell Extracting formula. I have cells that contain a persons first and last name along with some characters I would like to omit. Like this: John Doe;#1234 And I also have cells with more than one name like this: John Doe;#1234;#John Doe;#1234. Trying to come up with a formula that just displays the name and ignores the semicolon, pound sign, and numbers. Thanks in advance to anyone that can come up with a effective formula for both scenarios.

A straight Excel formula solution is not coming to mind for me, but I do have a UDF (user defined function) solution for you to consider...

Function RemoveNonNameParts(S As String) As String
  Dim X As Long, Parts() As String
  Parts = Split(S, ";#")
  For X = 0 To UBound(Parts) Step 2
    RemoveNonNameParts = RemoveNonNameParts & ";" & Parts(X)
  Next
  RemoveNonNameParts = Mid(RemoveNonNameParts, 2)
End Function

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use RemoveNonNameParts just like it was a built-in Excel function. For example,

=RemoveNonNameParts(A1)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[István Hirsch]

Can we say that you want to delete everything after the first semicolon?

 

[Rick Rothstein]

Can we say that you want to delete everything after the first semicolon?

No, I don't think so given the OP said this...

"And I also have cells with more than one name like this: John Doe;#1234;#John Doe;#1234."

 

[3xxx]

woah 7 pages, amazing read



this one should be MORE ADVANCED than the above


EDI is old technology, but still need programmer to do it


there is a lot of prefix -- to be identify and trace -- probably we(I) could use THE METHOD that you suggest in this thread



JUST a general question here, maybe I expand later (could be EDI or payment gateway-- how to do those 2)


I expect a general answer too


..Q)

when we read more about EDI

there is data translation and data mapping


could you give us a few example how to do it, with EXCEL, esp with VBA code

(till now Q2>>, let say, I have an expression or those formula in Q2 cell; is there a general (easy way) to translate to VBA code i.e. one line expression (begin with "=", and the VBA code can be
either program something with same result --- expression --> vba code
or
ask VBA code to get the formula in this cell (with the express) and evaluate it, put it inside a DIM xx as STRING variable inside the code) -- end of Q2

Q1 is about google some EDI raw text file, and translate those REQUIREMENT (as an IT programmer, we have to do it) and to become meaningful EDI translation, some data mapping might be included, e.g. AMEX become americanexpress, MC become mastercard

and then AMEX bank or MC bank may need to further process those data into certain EDI number (acceptable format for bank to auto-possess it)

i.e. without those EDI consultant, but using excel to do those EDI software task --- requirement will be specific for 2 parties (company) that try to exchange info (put it in their own company way to be understood)


hope that I explain the matter clearly
1. google about some sample EDI
2. know what EDI software usually do

3 . do you think it is possible to use EXCEL VBA to do it, with few mrexcel link on tackle each hurdle (data mapping, EDI translation ) -- could be as simple as VLOOKUP on a worksheet - then it is the generalization answer that I am expected, from expertise like you, thanks

 

[3xxx]

for the above Q1 -- found some example

http://imgur.com/cNKJBKw.png

becoming


saw the HIGHLIGHT in the LEFT ===> corresponding to Something on the RIGHT


http://imgur.com/hMQI9IN.png


next item - is NOT so vital (just additional info)
also got SEF (I guess it is data mapping | data translation) and some x12 file which is Raw File of EDI

http://imgur.com/tHdxpDV.jpg

 

[zubair99]

Yes its works for extracting specific strings from the cell required.

=TRIM(RIGHT(SUBSTITUTE(A1,"///",REPT(" ",LEN(A1))),LEN(A1)))

Thanks Mr. Excel you are great.