Results 1 to 4 of 4

Count characters in each word of a cell

Threaded View

  1. #1
    Registered User
    Join Date
    08-14-2017
    Location
    St. Pete, Florida
    MS-Off Ver
    0365
    Posts
    2

    Post Count characters in each word of a cell

    I have a list of 12K names to parse. All different formats. First Name Last Name, Some have initials some do not, some have suffix and some do not. Additionally, sometime they have 2 first names or 2 last names. Therefore I need to group similar data and review manually for these anomalies, which in my case are more the norm than not. Therefore a straight text to columns won't work well as I will spend more time moving data back together.

    I built a formula in B2 for "How many words in each cell" - this is the first part of my approach (used for sort/grouping only)
    I now need to see in B3 (or how many other cells are needed), how many characters are in each word. This will allow me to sort by B2 first, and then by B3.

    B1 = Cheryl Marie A. Morrow Walsh
    B2 = 5 (number of words in B1)
    B3 = 6, 5, 2, 6, 5 (this is the # of characters in each word from B1)

    Now I can group/sort my data by B2 & B3. This will put all like data together so that I can use the Text to Columns feature on a large group of same format records at one time. I will use the Fixed Width and NOT the delimited. This way I can parse the double first and double last names easily.

    I have the formula to count the # of characters in the FIRST word, but then I can't get the formula to work after that. Here is what I am using - =LEN(LEFT(B1,FIND(" ",B1,1)-1)). I tried copying this into B4 and just changing out the [start num] from 1 to a 7 to try and begin counting at the next space / word - But... no luck.

    Can anyone offer some help?
    Thanks
    Last edited by CherylWalsh71; 08-14-2017 at 03:38 PM. Reason: fixed spellings

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Count words and characters of hyperlinked word documents?
    By asterixo500 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-19-2015, 12:39 PM
  2. Macro that removes word based on characters in cell
    By wonderd in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 02-19-2015, 10:07 PM
  3. [SOLVED] Can I count characters in a cell
    By tuph in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-26-2014, 10:09 PM
  4. [SOLVED] find word in string, count how many special characters are to its left
    By jordan2322 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-04-2013, 06:46 PM
  5. [SOLVED] Formula needed for Removing Certain word + characters up to next Comma in a Cell
    By alw99 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-03-2012, 11:07 AM
  6. Add the word ERROR in the 133rd position after a string of characters in a cell
    By viantahu in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-24-2012, 04:32 PM
  7. Count Characters in a Cell
    By TOTO_POP in forum Excel General
    Replies: 2
    Last Post: 08-06-2010, 06:03 AM

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