**Question description:**

*This user has given permission to use the problem statement for this blog.*

I need section of text deleted in a column . Problem is the cell in each column Is html and seems it will be a challenge but if you can do this , you are genius

Solved by B. A. in 60 mins

This is the chat thread from the real Excelchat help session. It contains no private user information.

User
12/02/2018 - 01:57

Hi there I cut and pasted the spreadsheet

User
12/02/2018 - 01:57

Anyone here?

User
12/02/2018 - 01:57

hopefully i'm not wasting this session :)

Excelchat Expert
12/02/2018 - 01:57

Hi

User
12/02/2018 - 01:58

So as you can see Column C has rows of information that is html

Excelchat Expert
12/02/2018 - 01:58

YES

User
12/02/2018 - 01:58

I need to remove chunk of words, paragraphs

User
12/02/2018 - 01:58

and have certain areas left untouched

User
12/02/2018 - 01:59

so if you go to https://html-online.com/editor/

User
12/02/2018 - 01:59

and copy a cell onto that you can see what information there is.. Lets go for example content on C2

Excelchat Expert
12/02/2018 - 01:59

ok

User
12/02/2018 - 02:00

So you can see there is lot information and for this particular cell, I need to keep the following text

User
12/02/2018 - 02:00

Product Details
Item Description
Brand New Officially Licensed Product
Approximate Measurements in Inches
Size
Width
Length
Sleeve
S
18""
27""
22.25""
M
20""
29""
23.25""
L
22""
31""
24.25""
XL
24""
32""
25.25""
2X
26""
33""
26.25""
3X
28""
34""
27.25""

User
12/02/2018 - 02:01

And everything above this or below this needs to be deleted is this possible?

User
12/02/2018 - 02:01

just cluttere of info that is not needed

Excelchat Expert
12/02/2018 - 02:01

let me check that info

User
12/02/2018 - 02:01

ok

User
12/02/2018 - 02:11

Do you know what happens when time runs out ?

User
12/02/2018 - 02:11

as 4 minutes seems to be left only

Excelchat Expert
12/02/2018 - 02:11

Ok I have found a way

User
12/02/2018 - 02:12

please share

Excelchat Expert
12/02/2018 - 02:12

here

[Uploaded an Excel file]

User
12/02/2018 - 02:12

seems time is almost gone. I'm wondering if they will give extra time allowance

Excelchat Expert
12/02/2018 - 02:13

Please note your subscrpits are coming to....<h5> tags...<td>...etc.

Excelchat Expert
12/02/2018 - 02:13

column D

User
12/02/2018 - 02:13

guess they provided 20 minutes extra pheww

Excelchat Expert
12/02/2018 - 02:13

yes :)

Excelchat Expert
12/02/2018 - 02:13

ok look at column D

User
12/02/2018 - 02:14

from the download?

Excelchat Expert
12/02/2018 - 02:14

yes i have attached a file

Excelchat Expert
12/02/2018 - 02:14

have you found it?

User
12/02/2018 - 02:14

yes its highlighted

Excelchat Expert
12/02/2018 - 02:14

yes

User
12/02/2018 - 02:15

is this is a formula to apply to the whole column?

Excelchat Expert
12/02/2018 - 02:15

Yes

User
12/02/2018 - 02:15

to remove above and below the content that needs to be saved

User
12/02/2018 - 02:15

in this case

User
12/02/2018 - 02:15

Product Details
Item Description
Brand New Officially Licensed Product
Approximate Measurements in Inches
Size
Width
Length
Sleeve
S
18"
27"
22.25"
M
20"
29"
23.25"
L
22"
31"
24.25"
XL
24"
32"
25.25"
2X
26"
33"
26.25"
3X
28"
34"
27.25"

User
12/02/2018 - 02:15

everything else needs to go away

User
12/02/2018 - 02:16

how can I apply this formula to the rest of the rows?

Excelchat Expert
12/02/2018 - 02:16

The full column D is now under this formula

Excelchat Expert
12/02/2018 - 02:16

The part you are looking for is in Column D...()yes with h5..td tags

Excelchat Expert
12/02/2018 - 02:17

now if you need to delete col C we can do that after extracting D from C you can copy it and keep its to another column ....then C can be deleted

User
12/02/2018 - 02:17

Ok I saw that it applied to each row and that it filtered out the unwanted parts I mentioned

User
12/02/2018 - 02:18

Looks great EXPERT!

Excelchat Expert
12/02/2018 - 02:18

:)

User
12/02/2018 - 02:18

Can I do a little tweak

User
12/02/2018 - 02:18

So this formula of yours

User
12/02/2018 - 02:18

Let say the rows don't all have same format and I see that some rows are different format..

User
12/02/2018 - 02:19

Can I tweak the formula so that I can apply it o this as well.. AND for future sheets I need to be working on

User
12/02/2018 - 02:19

I can give you another sheet real quick and see if it can apply to a different sheet..

Excelchat Expert
12/02/2018 - 02:20

In each case you need to identify where your product paragarph starts and ends

Excelchat Expert
12/02/2018 - 02:20

Here I was looking for the starting line of the product and the ending line

User
12/02/2018 - 02:20

So basically have to find which paragraph I want to preserve

User
12/02/2018 - 02:20

and the rest will be deleted?

Excelchat Expert
12/02/2018 - 02:21

Yes, in short so

User
12/02/2018 - 02:21

But i'm guessing the body and html coding in beginning and end needs to be preserved cauase its html right

User
12/02/2018 - 02:21

or the format won' be preserved

User
12/02/2018 - 02:21

Pardon my French but your fucking genius

User
12/02/2018 - 02:22

I have to go church soon too lol

Excelchat Expert
12/02/2018 - 02:22

you can keep the html

Excelchat Expert
12/02/2018 - 02:22

If you need

Excelchat Expert
12/02/2018 - 02:22

Should I add that?

User
12/02/2018 - 02:22

yes

User
12/02/2018 - 02:22

how can I continue this session after it closes and ill pay for the extra session.. Will it be same person?

User
12/02/2018 - 02:23

So this formula can be applied to future sheets that have lot of unwanted text in html and keep the ones I want>

User
12/02/2018 - 02:23

?

User
12/02/2018 - 02:23

if yes, is it possible to do a quick step by step direction on tweaking theformula

User
12/02/2018 - 02:23

cause looks great now..

Excelchat Expert
12/02/2018 - 02:23

here

[Uploaded an Excel file]

Excelchat Expert
12/02/2018 - 02:24

If you are coming back today I sure will try to reach you

Excelchat Expert
12/02/2018 - 02:25

You can just post your problem in the portal same way you did, if not me any other good expert hopefully will help you

User
12/02/2018 - 02:26

So they will be able to start from where we left off?

Excelchat Expert
12/02/2018 - 02:26

Yes just give the description as per your need

User
12/02/2018 - 02:26

is this excel download one here is this steps?

Excelchat Expert
12/02/2018 - 02:26

you need to know the steps?

User
12/02/2018 - 02:27

Yes that would help this newbie

Excelchat Expert
12/02/2018 - 02:27

that will take some time to explain it to you

User
12/02/2018 - 02:27

seems we got 9 minutes for aquick tutorial step by step

Excelchat Expert
12/02/2018 - 02:28

if you have some idea on excel functions then its like this

User
12/02/2018 - 02:28

so this is essentially formula here right

User
12/02/2018 - 02:28

=CONCATENATE("<html>", " ",MID(C198,SEARCH("<h5>Item

Excelchat Expert
12/02/2018 - 02:28

Three main functions

Excelchat Expert
12/02/2018 - 02:28

Yes

User
12/02/2018 - 02:29

and this would be universal to future sheets to removal of unwanted text while preserving the one I need right?

Excelchat Expert
12/02/2018 - 02:29

so from the very core Search funcrtion is determining the positions of beginning and ending of the paragaraph

Excelchat Expert
12/02/2018 - 02:29

Yes

Excelchat Expert
12/02/2018 - 02:30

You just need to insert the right parameters

Excelchat Expert
12/02/2018 - 02:30

Such as for this case your <h5>item description</h5> is the beginning string

Excelchat Expert
12/02/2018 - 02:31

look that I have enterd this string in search to find the positiion

Excelchat Expert
12/02/2018 - 02:32

also the last line would be the beginning of next paragraph

User
12/02/2018 - 02:32

Ok do you see sample of new html content I just pasted

User
12/02/2018 - 02:32

For example Iet say i'm preserving the following

User
12/02/2018 - 02:32

OFFICIALLY LICENSED NFL TEAM APPAREL..

User
12/02/2018 - 02:32

Your formula, how would I go about this one?

Excelchat Expert
12/02/2018 - 02:33

let me check

User
12/02/2018 - 02:33

if I have two references then i'll be able to see what you mean easier

Excelchat Expert
12/02/2018 - 02:34

OFFICIALLY LICENSED NFL TEAM APPAREL.....this will be input of one search fynction

Excelchat Expert
12/02/2018 - 02:34

so if this part is the last part of the total cell

User
12/02/2018 - 02:36

Is there way you can actually write the formula for this one and I can see what changes you did for both and get he hang of it.. I'm a visual learner for sure

User
12/02/2018 - 02:36

or hands on type

User
12/02/2018 - 02:36

I majored in art hah

User
12/02/2018 - 02:40

still here?

Excelchat Expert
12/02/2018 - 02:40

yes wait a bit

User
12/02/2018 - 02:40

no just checking thought it disconnected

Excelchat Expert
12/02/2018 - 02:41

check

Excelchat Expert
12/02/2018 - 02:42

So here as the searching part is the end part

User
12/02/2018 - 02:43

=MID(A3,SEARCH("OFFICIALLY LICENSED NFL TEAM APPAREL..",A3),LEN(A3)-SEARCH("OFFICIALLY LICENSED NFL TEAM APPAREL..",A3))

User
12/02/2018 - 02:43

is this formula?

Excelchat Expert
12/02/2018 - 02:43

Yes

Excelchat Expert
12/02/2018 - 02:44

Look here the part to be extracted is the last paragraph

User
12/02/2018 - 02:44

So does this formula work let say that the description i'm keeping is different for each cell.. but the header and footer is all same .. can I delete the header and footer and keep the content in middle with this formula?

Excelchat Expert
12/02/2018 - 02:44

In other words total length-length upto OFFICIALLY....

Excelchat Expert
12/02/2018 - 02:44

Yes.

Excelchat Expert
12/02/2018 - 02:44

as long as this paragrapgh is the last part

Excelchat Expert
12/02/2018 - 02:45

And if this paragraph is in middle, then the previous formula should be used

User
12/02/2018 - 02:45

So the same formula was applied to the two samples I provided?

Excelchat Expert
12/02/2018 - 02:46

No...As I am saying

Excelchat Expert
12/02/2018 - 02:46

In the first case

User
12/02/2018 - 02:46

ahh isee

Excelchat Expert
12/02/2018 - 02:46

Your paragraph was in middle

Excelchat Expert
12/02/2018 - 02:46

so you need to identify the beginning and ending of the paragraph

Excelchat Expert
12/02/2018 - 02:46

by two search functions and the beginning and ending strings

Excelchat Expert
12/02/2018 - 02:47

And in later case OFFICIAL.....your paragraph is the last part

User
12/02/2018 - 02:47

I see so two separate formulas and use accordingly to one that applies

User
12/02/2018 - 02:47

right?

Excelchat Expert
12/02/2018 - 02:47

Yes

User
12/02/2018 - 02:48

So three is a good number.. I posted one final sample. and wondering which formula 1 or 2 this one applies

User
12/02/2018 - 02:48

Need to keep the following text

User
12/02/2018 - 02:48

Officially Licensed Product
3 Ft. by 5 Ft. flag with two heavy duty brass grommets
One sided flag with official team graphics (reverse image on other side)
Made of heavy-duty 150 denier polyester (100% polyester)
Great for home decor and outdoor use
Has an extra wide headband
Display your school colors proudly!
Flag packaged in retail packaging
Item #95749

User
12/02/2018 - 02:48

and wipe out anything above and below

User
12/02/2018 - 02:50

You guys got skills and utilize in time sensitive pressure.. probally can work for bomb squad haaha

Excelchat Expert
12/02/2018 - 02:50

haha, is you new post in row 11?

User
12/02/2018 - 02:50

yes

User
12/02/2018 - 02:51

I always thought html did some blockage of removing words and pargaraphs... but proved me wrong.. this is a powerful program indeed

Excelchat Expert
12/02/2018 - 02:53

look here there is a good hind that # will come

User
12/02/2018 - 02:53

what number?

Excelchat Expert
12/02/2018 - 02:53

This will be the key here

Excelchat Expert
12/02/2018 - 02:54

NO I meant the symbol #

User
12/02/2018 - 02:54

ok

User
12/02/2018 - 02:54

and whats the signficace of this?

User
12/02/2018 - 02:54

2 minutes before bomb explodes

User
12/02/2018 - 02:54

I shouldn't reference anything to bombs sorry

Excelchat Expert
12/02/2018 - 02:54

As the first # after OFFCIALY...is almost the last part of our ectraction

Excelchat Expert
12/02/2018 - 02:55

*extraction

User
12/02/2018 - 02:55

ok so the formula is done? I think I can see all three formulas and

Excelchat Expert
12/02/2018 - 02:55

wait

User
12/02/2018 - 02:55

see what you did.. that's how I seem to work

**This is the output file from the real Excelchat help session:**

*This is an example of the expert help you can get. It contains no private user information.*