Counting text in a column of cells with >256 characters per cell

GABeach

New Member
Joined
Oct 10, 2014
Messages
1
Hi

Please let me know the best and simplest ways to count words and phrases in a column of many cells, where each cell may contain text much more than 256 characters in length. The details of what I need to do are below.

Thanks very much!

I need to count the occurrences of words and phrases in text that is copied and pasted into Excel from websites.

If just copied and pasted into a sheet from a page on the website, the breaks in text due to HTML links on the page (I assume that's the reason), etc. result in many sentences and phrases being split across 2 or more cells.

To avoid that, I'm pasting the full text from each page into a single cell (1 web page copied and pasted into 1 cell), which almost always results in more than the 256 character limit to display the cell contents as text instead of the ################################# that Excel shows for text format >256 characters in a cell.

I don't know if the problem I'm having is due to the 256 character text format limit (as opposed to formatting the cells in the column as "General", which makes them readable), but am including this detail in case it's important.

I'm using Excel 2008 for Mac.

For example, one sentence in the text below, from one of the cells, says "eXtra Large Eyeglasses Frames for the man with the Large Brain-Box", but is split across two cells as such:

eXtra Large Eyeglasses Frames for the man with the
Large Brain-Box

I need to be able to count the occurrence of words such as "vintage" and phrases such as "the man with the Large brain-box" (they're not all such humorous phrases :).

Generally there are fewer than 100 cells in column, but I have hundreds of words or phrases that I need to count for each website (equating to those 100 or so cells of text).

When I copy and paste the text, allowing it to split across multiple cells I can get the COUNTIF function to work for single words (but not phrases since as mentioned, they're split across cells), e.g.

=COUNTIF(D7:D1048576, "vintage")

BUT it will not pick up and count EITHER words or phrases when the entire contents from a web page are pasted into a single cell.

The desired output would look like this:

Vintage 23
the man with the Large brain-box 2
....etc.

Since I do need to count phrases (and to do that they can't be split across cells), not just individual words like "vintage" I'd prefer to be able to keep the one web page to one cell approach I'm currently using for the pasted text.


HERE IS ACTUAL TEXT FROM ONE OF THE CELLS I'M WORKING WITH, PASTED HERE DIRECTLY FROM IT:


Make payments with PayPal - it's fast, free and secure!
SOLD
Make payments with PayPal - it's fast, free and secure!
Vintage
Catseyes -
Black &
Silver
Make payments with PayPal - it's fast, free and secure!
SOLD
Check back for more vintage cat eyes & retro sunglasses, eye glasses & frames updates
Vintage Cat's Eye
Silver & Black







These frames are all New-Old-Stock, never worn, vintage. They haven't been made in
years, chances are no one else has a pair.


Always vintage and always pointy... 50s
Cat Eye Frames.

Small Rhinestones Black Gold Silver
Gold Cat Eye Frames
Make payments with PayPal - it's fast, free and secure!
Large Plumb Colored Cats
Medium
Make payments with PayPal - it's fast, free and secure!
60s Small Blue with Cosmic Gold
Mod 60s Large
Art-Craft Large



Black - Silver Large
Medium
Small Gray-Smoke Cats
Med/Large 60s Rhinestones
Star-Girl black cats eye glasses
50s-60s Cat's Eye, Black Med
*****-Footer glasses
NEW GLASSES GIVE LIFE A LIFT

Frame France Wrap, Medium
Medium
Medium Rhinestones

Want to see
More, Click Here
Want to see
more, Click Here
Celebs In Specs
Medium Aluminum Readers, 60s
Shuron RonLady Cat Eye eyeglasses
New Black Large
Swank Frame France vintage eyeglasses
Small Black&Silver 60s
Small/Med Frame Vienna 60s
pointy cats eye glasses Shayla
Large Black Italian 60s

Rhinestone 60s Small
Small FriezeFrame Rhinestone
Large Demi-Amber Cats
Large 60s Unisex
Medium
Maria Viva cat eye eyeglasses rhinestones
Java Tan Rhinestone Small
Super-Hip vintage cat eye eyeglasses with rhinestones


Frame Isreal Eyeglasses Vintage oversized 70s eyeglasses

















60s Catseye - France Medium
Small Deco 50s-60a
Size?
Returns
F.A.Q
Contact Us
About Us

Victory Optical Cat Eye Blue Large

Medium Awesome Green
Small Tart Rhinestones

Large Round 60s-70s

Add Lenses
vintage flair originals, blue cat eye frames
Small Blue 60s
Med/Small Frame France 60s
Hipster Geek Chic Goth 50s 60s Eyeglasses
60s French New Wave 60s Large



Red 60s Italy Large

Large & XXL
Frames

Large Black Bats-Eye Rhinestone
Pointy Black/Blue Large
Pointy Red Large
Pointy Dress Black Cat Large
Small FriezeFrame Rhinestone
Small FriezeFrame Rhinestone

SOLD
SOLD


Make payments with PayPal - it's fast, free and secure!
SOLD
The Best Selection of Vintage RayBan Sunglasses, Antique Sunglasses, and Retro Eyewear






And a huge honkin line of retro


shades




50s-50s Vintage
Make payments with PayPal - it's fast, free and secure!


And a huge honkin line of retro


shades
Contact Us
Questions?
Cabana 50s-60s
Large Italian Sunnies
Polarized G-Man Sunglasses
1960s Fellini, Bob Dylan
Adidas Golf Sunglasses
70s Italian

1960s Italian Sunglasses
Christian Dior Large
60s Black Frame France
Italy
Shuron Optical USA, New Polarized Club
New, Polarized Club Sunglasses
Made in Italy
Frame Italy
1970s Italian
Black & Silver Clubmaster
Vintage Ray-Ban Lisbon
G-Man, Club Glasses
1960s A. Sutain, Japan
Retro LuLu Guinnes
Glamorous 60s-70s
Large 50s Ray-Ban Dekko
Karen Walker Oversized
Small 60s Cat-Eyes
Shuron Optical USA, Polarized G-Man Sunglasses
70s Made In Italy
Fellini Style 60s
Red Persol
Shuron Optical USA, Combo Clubman, 100% UV Protection Shuron Optical USA, 100% UV Protection G-man Club Glasses

Women's Vintage & Retro
Eyeglasses, Frames
Vintage Cat Eye Glasses Frames



Small Green Rhinestone
Vintage Carrera XL 70s-80s
Med/Small 60s Frame France
Med-Large Cat Eye Glasses
Click on photo for more photos &
information


Women's Vintage & Retro Frames





Large 70s
Click on photo for more photos & information






Womens, ladies, pointy catseyes, occhiali, lunette, ottica, bay area vintage, di sole, Jackie O, Designer Frames



Size?
Large & XXL
Frames
Shipping &
Returns
Add Lenses
F.A.Q
Contact Us



Vintage 70s Oversized GlassesLarge black 70s eyeglasses
Medium 70s
Medium 70s
Large Victory Optical
Medium 70s
Small Black 50s-60s CatsEye
Medium 70s
Frame Italy Medium 60s
Regency Optical Black and gold 1950s eyeglassesSafilo Black Italian Eyeglasses
Medium/Small Catseye Frame 60s
Small 60s Frames
Large Victory Optical
Small Black Blue Pearl A/O
Large Rhinestone France
50s Art-Craft Gold Medium

Medium Bausch & Lomb 60s
Small Blue 60s Cats

Medium Tart USA
Frame Italy 60s Medium

Green - Medium
Large Tortoise
Fade - Med

Small Mod Frame France
Small Black-Marble
50s Med/Small France

Large Tart USA
Large Black Frame Italy
Large Black 60s USA Eyeglasses
Small 60s Aqua SALE
50s France Medium Black

Medium Cat-Eye 1960s
Black Tart Med/Small
Med/Small 12K Gold 50s-60s Cats


Frame France 60s - Medium
About Us
Large Rhinestone Cat USA

Med-Small 60s
Large
Actual Bamboo - Large
Bamboo

Medium Black Vickie
Large Victory Catseye
Large Rhinestone Cat USA





Medium/Small
1/2 Price
1/2 Price
1/2 Price

SOLD
SOLD
SOLD

SOLD
SOLD
SOLD
SOLD
SOLD




Large Frame France Swank
Med/Large Frame Cats
Medium Art-Craft 12k Gold

Frame France Med/Small
Small Black Rhinestone
Tortoise Cats Sale
50s-60s Large Mod France
Rhinestone Large
Small Small Copper 60s
Large Green Cats


Blue/Crystal/Silver - Small
Small Star-Girl
X Large 70s-80s Playboy
Small Frame France, Special Sale
Large Rhinestone


Small Mod 70s
Large Cats 60s


Med/Large Cats 60s
Large Mod Frame France 50s-60s
Crystal Mod 60s Small
Frame Italy Black Medium
12k White Gold
Large Black 60s


Titmus Dark Red, Med 60s
Medium Rhinestone 60s


60s Rhinestone, Med & Small
Special Sale, Black/Gold 60s
Small Art-Deco Cats 50s-60s

Small Vintage Rhinestones
Medium Frame Austria 60s
Tortoise Medium Cats
70s Med/Large
Mod 60s France Medium




Medium Black Italy

Size?
Large & XXL
Frames
Returns
Add Lenses
F.A.Q
About Us

Medium LG Mod France


Frame France Eyeglasses, Mod 60s Frames
Flair Specs Original Med-Large
50s-60s euro hipster new wave cat eye glasses
Contact Us

Medium Gold Rhinestone Cat-Eye
Medium Silver Rhinestone Cat-Eye
Small Pearl Cat-Eye, Rhinestone
Small Bronze Cat-Eye, Rhinestone
Large Black Mod Wrap Rhinestone

SOLD
SOLD
SOLD
SOLD
SOLD

SOLD


Next to sniffing glue,
new sunglasses
make me the happiest
B&L Ray-Ban
Star-Girl Small
2 Sizes, Producer
1960s Sunglasses, Classic thick, black, hand made Zyl frames Adidas Golf, Frame Austria, Sunglasses 1960s Fellini Style, Thick Plastic Sunglasses with Big Hinges, New Old Dealer Stock
1960s Fellini Style, Italian
Courtland Club Master 1940s style in the Clubman, Gman Tradition Ultimate 60s hipster vintage sunglasses Vintage Italian Sunglasses, New, Old Stock 1970s Excellent 1970s Vintage Sover Italian Sunglasses, New, Old Stock, Excellent Made in Italy, Vintage Oversized Italian Sunglasses, 1970s Made in Italy Vintage Sover Italian Sunglasses, Excellent Condition Made in Italy, Vintage 1970s Vintage Sover Italian Sunglasses, Excellent, New Old Stock Vintage Sover Italian Sunglasses, New Old Stock, Excellent, 1970s Excellent Condition, Vintage Sover Italian Sunglasses, New Old Stock 1970s Glamorous 1960s - 1970s Vintage Sover Italian Sunglasses, Excellent Condition, Made in Italy
Safilo Italian Sunglasses
Fellini Inspired Sunglasses
B&L ONYX

Crystal/ Dark Lens - All Sizes
Fellini Inspired Sunglasses, New Old Stock, Condition Flawless, Thick Black Frames Ray-Ban Onix, Bausch & Lomb, New Old Stock with Case, Box, Papers
60s Victory Large
Black Ray-Ban Olympian
Oversized White 60s
1960s Italian Sunglasses
1960s Italian Sunglasses, Thick Tortoise, Hand Made Zyl Frames, Green Lenses
Want to see
more, Click Here
Celebs In Specs
Want to see
more, Click Here
Small Cats Eyes
London, Retro LuLu Guiness Sunglasses
60s Ray-Ban Ladies
50s-60s Cats Eyes
Ray-Ban Onyx, Bausch & Lomb WO 794, Tortoise Wrap Frames, Frame France
RB Onyx, Bausch & Lomb

RB Onyx, Bausch & Lomb
B&L Ray-Ban USA, Frame France Tortoise Wrap Frames WO 789
RB Onyx, Bausch & Lomb
Ray-Ban Onyx, Bausch & Lomb, Ebony Strata Frames Ray-Ban Olympian, Bausch & Lomb, WO-791, Frame France
RB Onyx, Bausch & Lomb

Art-Deco Alum Med
Frame Italy
Karen Walker Designer Sun
Combo Clubman
Safilo Italian Sunglasses, Ammans, Frame Italy, Black, Retro Italian Made
Victory Fairhaven - Large
The Producer, Courtland, Heroes of Leisure, As seen on Johny Depp & James Dean Black Ray-Ban Olympian, Bausch & Lomb, G-15 B&L Lenses Ray-Ban Bausch & Lomb Olympian III, Mind Old Stock with Box & Papers
B&L Ray-Ban Olympian
Shuron Optical USA, Freeway
Art Deco Aluminum, Courtland Optical, Zylite Aluminum Art Deco Aluminum, Courtland Optical, Matte Finish 50s G-Man Style Art Deco Aluminum, Courtland Optical, Zylite Aluminum
Art-Deco Alum Med
Art-Deco Alum Med
Size?
Large & XXL
Frames
Shipping &
Returns
Add Lenses
F.A.Q
Contact Us
About Us
High Quality Reproductions of the 50s - 60s Tru-Vue, Star Girl, Black
50s Euro Sunnies
Oversized White 60s
Mod 60s France Sunglasses

B&L Ray-Ban Wayfarers
Frame Italy, LARGE
60s Cay Eyes, Vintage Frame USA 1960s Bausch & Lomb Ray-Bans W1946 PNAS, Green G-15 Lenses
Crystal Esquivel
Beat Jazz Masters
Thick Black Esquivel LG
Juan G Esquivel Frame, Beat Jazz Masters, Thick Hand Made Acetate, Steel Reinforced Temples Crystal Esquivel Frame, Steel Reinforced Temples, Heavy Hinges Juan G Esquivel Frame, 50s Euro Sunnies Thick Black Equivel, Original Mexican Medicare Frames Frame France, Smoothie, Swank 50s - 60s, Old Optometrist Stock Mint
Large Victory Optical
Large Victory Optical
60s Ray-Ban Mods, Bausch & Lomb Used Excellent Condition
60s Ray-Ban Mods
Cabana USA. 50s-60s, New, Old Optometrist Stock
Large Amber Wrap
Mens Mod Large
Mens Mod Large
Victory Optical 1960s
Vintage Italian Sunglasses
Large Victory Optical

Med/Small w Violet Lens

Med/Small w Violet Lens
SOLD
Vintage Wayfarers For Sale
SOLD

SOLD
SOLD
SOLD

SOLD
SOLD
SOLD
SOLD
SOLD
SOLD
SOLD

SOLD
SOLD






eXtra Large Eyeglasses Frames for the man with the
Large Brain-Box
XL Black & Silver Combo
XL Black & Black Combo
XXL Gold 70s
Tart Arnel Repro XL
XL Deco Victory
XXL Gold Wire-Rim 70s
XL Tortoise Italian
XL Mod Fade


XL Black Mod
Mod Fade XL


Check Your Size At Home
XL Black Mod
XL Black Mod
A huge selection of Large, Extra
Large, and XXL Vintage eyeglasses
frames. Retro eyewear.

XL Black Sidewinder


XL Tortoise Sidewinder


Mod Gray-Fade


XL Gold Aviator 70s
Large Black or Tortoise
Lo-Rise Black Combo
XL Black


Pathway Challenger 50s
Tortoise XL Victory
Gray XL, XXL

Arnel Repro Honey/Tortoise
Large Gray-Fade
Fade Sidewinder
G-Man, 3 Sizes, XL
XL Gold

Pathway Challenger 50s
2-Tone Producer XL

Large Black 50s-60s

Black XL Victory
Combo Victory XL

Size?
Large & XXL
Frames
Shipping &
Returns
Add Lenses
F.A.Q
Contact Us
About Us
XXL Black

Black Oversized
Mod Tortoise XL
XL Mod 70s

Mod Black XL
Large Round Gold
Kala Chateau
XL Ultra-G
Huge Goliath Black
Huge Goliath Demi-Amber

XL Bowler 50s-60s

SOLD
Buddy Holly XL Eyeglasses Black
XL Ultra-Thick
XL Beat
Tortoise Jazz King

50s Esquivel
Kala Chateau
Saphira Germany XL
XXL Dior Uni-Sex

SOLD

SOLD
SOLD

SOLD

SOLD

SOLD
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Watch MrExcel Video

Forum statistics

Threads
1,129,917
Messages
5,638,981
Members
417,061
Latest member
thematulaak

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top