Extract Specific text again

[king10001]

I have a numerous cells 18,000 of them that I want to extract specific text from

Each cell looks like this

AuthorJerry Seigel
ArtistJoe Shuster
ArtistJack Burnley
ArtistLeo Nowak
ArtistJohn Sikela
ISBN-139781401201517
FormatHardback,
PublisherDC Comics
Publication date2 Jun 2004
Pages224
Product dimensions178 x 254 x 25mm
Weight771g

I need to separate the text so that it take out " Hardback", " DC Comics", "224", separates the 178 254 and 25, and also takes out the 771g.

This is way out of my league when it comes to excel.

[Kaper]

Will you please attach a SMALL sample Excel workbook(s) (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

1. Make sure that your sample data are truly REPRESENTATIVE of your real data. (If trere are typical cases like: all unique values/duplicates could occur, day/night, nobody present/several persons at once, before/on/past due, etc. - please show them all or at least indicate in text) The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution(s) is/are also shown (mock up the results manually).

3. Make sure that all confidential/restricted information (either personal or business) like real e-mails, social security numbers, bank accounts, etc. is removed first!!

To attach an Excel file you have to do the following: Just before posting, scroll down and press Go Advanced button and then scroll down and press Manage Attachments link. Now follow the instructions at the top of that pop-up screen.

[shukla.ankur281190]

May be try

B1

Please Login or Register  to view this content.

copy down!!

[king10001]

Here is the file as requested

Quick amendment Column F, so not to confuse should bring out Pages before 224.

[shukla.ankur281190]

Try

D2

Please Login or Register  to view this content.

Copy across

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

[samba_ravi]

D1=Format E1=Publisher F1=Pages G1=Dimensions and h1=Weight
then use below formula in D2 and copy across
=IFERROR(TRIM(LEFT(SUBSTITUTE(SUBSTITUTE(MID($C2,SEARCH(D$1,$C2)+LEN(D$1),256),",",REPT(" ",256)),CHAR(10),REPT(" ",256)),256)),"")

Please Login or Register  to view this content.

[samba_ravi]

further to bifurcate Dimension use below formulas
i2=TRIM(MID(SUBSTITUTE(SUBSTITUTE($G2,"x",REPT(" ",100)),"mm",REPT(" ",100)),1,100))
j2=TRIM(MID(SUBSTITUTE(SUBSTITUTE($G2,"x",REPT(" ",100)),"mm",REPT(" ",100)),101,100))
K2==TRIM(MID(SUBSTITUTE(SUBSTITUTE($G2,"x",REPT(" ",100)),"mm",REPT(" ",100)),201,100))
Try this and copy towards down

see the attached file

[king10001]

Superb It works

[AliGW]

You need to attach your file as requested in post #2.

[king10001]

on a few on the weights they have 4 digits instead of 3, the formula just pulls the 1st number out?

"AuthorHayao Miyazaki
Illustrated byHayao Miyazaki
ISBN-139781421500492
SeriesHOWL'S MOVING CASTLE
FormatHardback,
PublisherViz Media, Subs. of Shogakukan Inc
Publication date1 Jul 2005
Pages256
Product dimensions218 x 306 x 24mm
Weight1,596g"

[samba_ravi]

in attached file try below formula in H2 and copy towards down

Please Login or Register  to view this content.

[samba_ravi]

on a few on the weights they have 4 digits instead of 3, the formula just pulls the 1st number out?

"AuthorHayao Miyazaki
Illustrated byHayao Miyazaki
ISBN-139781421500492
SeriesHOWL'S MOVING CASTLE
FormatHardback,
PublisherViz Media, Subs. of Shogakukan Inc
Publication date1 Jul 2005
Pages256
Product dimensions218 x 306 x 24mm
Weight1,596g"

Dear, king10001

Please respond to the Senior forum members if they asked to do something or asked for something.

[king10001]

ignore it have it sorted.