+ Reply to Thread
Results 1 to 3 of 3

Segmenting strings based on capitalization (upper case) values

  1. #1
    Registered User
    Join Date
    06-21-2011
    Location
    Portland, OR
    MS-Off Ver
    2010
    Posts
    14

    Lightbulb Segmenting strings based on capitalization (upper case) values

    Hello. I've a list of 700+ text strings. Each string contains information in the following form:

    LICENSE NAME | DBA | Street address | City | Zip

    * | denotes a single space

    **ALL text associated with the license name is capitalized

    **The only field with a fixed number of characters is ZIP (5 characters)

    For Example:

    CARING LEVIS LLC CL Communications 1234 Main Street Portland 97206
    CROOKED LICENSE INC. Crooked Business Partners 501 First Avenue Suite 152 Eugene 92573
    CURVED & QUIET COLLECTIVE C&C Collective 10089 George Washington Parkway STE 680 Salem 90087

    I'd like to segment each string into five separate columns based on the color system above. Does anyone know of an efficient formula that can accomplish this task?

    Thanks for any help!

    -Matt

  2. #2
    Forum Contributor
    Join Date
    01-29-2009
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    123

    Re: Segmenting strings based on capitalization (upper case) values

    Try saving the file into a CSV, reopen it in Excel, you might see the single space (not the space in License Name or address) got converted to a symbol of some sort.

    If you see that, highlight the column and go to the DATA ribbon and click on TEXT TO COLUMNS and use that symbol as a delimiter.

    At least that's how I did it when I simply copy your examples to Excel.

    If not, try posting the actual file with just the three example you had in your original post.
    Last edited by glennchung; 10-02-2014 at 06:48 PM.

  3. #3
    Registered User
    Join Date
    06-21-2011
    Location
    Portland, OR
    MS-Off Ver
    2010
    Posts
    14

    Re: Segmenting strings based on capitalization (upper case) values

    Business List.xlsx

    Hey! Thx for the suggestion mate! Unfortunately, this route didn't yield any positive result.

    I've attached the first 20 strings from my list. TEXT TO COLUMN tool does enable me to segment everything into individual pieces. I'm sure I can figure out a way to put it all back together from that point BUT that is the long way. Hoping to learn a shortcut here.

    There is a misstatement in my OP. Both LICENSE NAME & DOING BUSINESS AS are represented with capital letters. This is the crux of the problem...

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Excel 2007 : Change Cell color based on upper of lower case
    By FDibbins in forum Excel General
    Replies: 3
    Last Post: 12-30-2011, 07:42 PM
  2. Conditional Formatting Duplicate Values (Upper & Lower Case)
    By Orangeworker in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-05-2010, 04:01 AM
  3. [SOLVED] Conditional Formatting based on Upper case
    By cs_vision in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-28-2006, 03:40 PM
  4. Replies: 14
    Last Post: 08-25-2005, 10:05 PM
  5. Replies: 1
    Last Post: 03-09-2005, 05:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1