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]

My overall project is big for me.
Media Center for the home.
1). Database from TVDB.com
2). Files on my media center.
3). Compare web data to my files.
4). Files being recorded from the media center compare to my files in my database and count
what seasons and episodes I have, and don't have. If it's been recorded and I have it already, then delete it.

My goal is to click one button

hope you understand?

 

[yeeloon]

Hi everyone,

In my current cell (A1), there's the content (group together) below:

Nationality: malaysian
ID: 8010106211
Phone: 0165027781

How can I extract the Nationality, ID and Phone into separate columns? Can someone advice?

Thanks,
yeeloon

 

[Rick Rothstein]

Hi everyone,

In my current cell (A1), there's the content (group together) below:

Nationality: malaysian
ID: 8010106211
Phone: 0165027781

How can I extract the Nationality, ID and Phone into separate columns? Can someone advice?

Assuming you have other data in the cells of Column A, select Column A, click the "Text to Columns" button (located o the "Data" tab, "Data Tools" panel)... select "Delimited" and click "Next", click in the field next the "Other:" checkbox and press CTRL+J (it will look like nothing happened, but if look at the table at the bottom you will see your data delimited into columns), click the "Finish" button.

You data will now be in separate columns, but with the identifiers still attached... if you want to remove them, select Columns A:C, press CTRL+H to bring up the Replace dialog box, put *: (that is an asterisk followed by a colon) in the "Find what" field, leave the "Replace with" field empty, click the "Options>>" button and make sure there is NO checkmark in the "Match entire cell contents" checkbox and then click the "Replace All" button.

 

[yeeloon]

Thanks Rick! It works perfectly!! Save me tons of my time..

 

[Rick Rothstein]

Thanks Rick! It works perfectly!! Save me tons of my time..

Actually, in looking at it again, I gave you "slightly" faulty advice... procedure I gave you to remove the identifiers will leave a leading space in each cell. Instead of using an "asterisk/colon" in the "Find what" field, you should use "asterisk/colon/space" first, then redo the replace with just the "asterisk/colon" only (the ordering of those two steps is important)... that way, you will get both those cells with a space character between the colon and text as well as those cells without a space following the colon.

 

[midrun2015]

Hi,
l would like to be able to seperate this text as follows:

DRMSTL55GL</SPAN>

BTLHPE40X0.2L DRMSTL and 55GL</SPAN> BTLHPE and 40X0.2L There seperation point will always be a number between 0 and 1. Please help </SPAN> <TBODY> </TBODY> </SPAN>

<TBODY>
</TBODY><COLGROUP><COL></COLGROUP>

 

[midrun2015]

well meant to say number will vary, so between {0 to 9}

 

[Rick Rothstein]

Hi,
l would like to be able to seperate this text as follows:

DRMSTL55GL

BTLHPE40X0.2L DRMSTL and 55GL BTLHPE and 40X0.2L There seperation point will always be a number between 0 and 1. Please help <tbody> </tbody>

<tbody>
</tbody>

More important that the separation being at the first number... will the first part that you want always be the first six characters with the second part being the remaining characters (as your two examples show)?

 

[MrWouters]

Hi,

New here, no excel genious, and not sure if I'm in the correct forum. So apologies in advance if I'm asking impossible things
BUT.... here's my problem
I want to extraxt only the fabric materials with (and/or without) their precentages.


I have for example below rows underneath eachother (just a small selection):

F10000019 Solid 67% Cotton, 28% Polyamide, 5% Elastane Plain Weave 114.0

F10005999 Print 100% Polyester Crepe 95gsm2 Woven

F10005537 100% Polyester 78gsm2 Woven

F10005273 Burn Out 56% Polyester, 44% Cotton Plain Weave 69gsm2 Woven

F10005273 Burn Out 56% Polyester, 44% Cotton Plain Weave 69gsm2 Woven

F10005278 Solid 59% Viscose, 41% Polyester Dobby 70gsm Woven

<colgroup><col></colgroup><tbody>
</tbody>

As you can see, they're different in the sense that not in every cell the "material definitions" start after the same amount of characters.

But for the first line for instance I would just want to have "67% Cotton, 28% Polyamide, 5% Elastane"
And for the second just "100% Polyester".

I know I can use the =MID formula but I'm not going to change the values for all the hundreds of lines of course.

Is it possible to use a formula that starts at the first % mark (and then -2 or something to include the number in front?) until the last % mark and then include the first word behind it?
Something like that? And that I can just pull it down the whole column?

Again, I dont even know if this is possible. But I know a lot IS possible in Excel, so it's a long shot, but who knows...

Forever gratefull in advance!!

Wouter

 

[István Hirsch]

If you want to process cells in column A, enter in B1 and copy down:

=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",99)),FIND("%",SUBSTITUTE(A1," ",REPT(" ",99)))-99,198*(1/2+LEN(A1)-LEN(SUBSTITUTE(A1,"%","")))))

Excel Workbook
	A	B
1	F10000019 Solid 67% Cotton, 28% Polyamide, 5% Elastane Plain Weave 114.0	67% Cotton, 28% Polyamide, 5% Elastane
2	F10005999 Print 100% Polyester Crepe 95gsm2 Woven	100% Polyester
3	F10005537 100% Polyester 78gsm2 Woven	100% Polyester
4	F10005273 Burn Out 56% Polyester, 44% Cotton Plain Weave 69gsm2 Woven	56% Polyester, 44% Cotton
5	F10005273 Burn Out 56% Polyester, 44% Cotton Plain Weave 69gsm2 Woven	56% Polyester, 44% Cotton
6	F10005278 Solid 59% Viscose, 41% Polyester Dobby 70gsm Woven	59% Viscose, 41% Polyester
Sheet