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.