Text to columns - using font size for delineation

gregfthompson
gregfthompson used Ask the Experts™
on
Worksheet contains a list in Column A. List content has two different font sizes.
Requirement is to copy the smaller font contents to Column B, and the larger coloured font contents to Column C.

Example for the first three rows is included.
EEExample.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
You can achieve that with the help of VBA.
Please find the attached with a button called "Separate Text" on Sheet1 to split the Text based on font size.
Since the Module1 also contains a User Defined Function called "SeparateText", you can also use that function on the worksheet like a regular excel function like this...

Assuming your text is in A1, then try this...

In B1
=SeparateText(A1,"small")

Open in new window

In C1
=SeparateText(A1,"large")

Open in new window

SeparateTextBasedOnFontSize.xlsm

Author

Commented:
Brilliant! Thank you very much.
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
You're welcome!
Thanks for the feedback!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial