Excel - IF Function Problem - Expert Solution

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.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.

Click here to get your free Excelchat help session

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc