+ Reply to Thread
Results 1 to 3 of 3

Insert character between lower case & upper case letters (ie. "VeryGood" -> "Very|Good")

  1. #1
    Registered User
    Join Date
    01-02-2012
    Location
    Melbourne
    MS-Off Ver
    Excel 2003
    Posts
    63

    Insert character between lower case & upper case letters (ie. "VeryGood" -> "Very|Good")

    Hi All

    I would like to insert "|" between a lowercase letter and uppercase letter when there's no space between the two ("BlueRed" -> "Blue|Red")

    I have data that looks like this (i underlined the problematic areas):

    A1 Jack JonesEnjoys playing cricket
    B1 Simon SimpsonGreat at golf
    C1 Bob BransonCommitted to improving his game
    D1 Dave DixonNot built for sport

    I'd like it to look like the following:

    A1 Jack Jones|Enjoys playing cricket
    B1 Simon Simpson|Great at golf
    C1 Bob Branson|Committed to improving his game
    D1 Dave Dixon|Not built for sport

    Is there a way to achieve this?

    Any help would be appreciated, thanks !

  2. #2
    Cheeky Forum Moderator Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Insert character between lower case & upper case letters (ie. "VeryGood" -> "Very|Good

    Assuming you want to avoid a VBA solution....
    With your sample data in A1:D1

    Using "helper cells" to reduce the complexity...
    This regular formula returns the location to place the pipe character
    Please Login or Register  to view this content.
    and this regular formula returns the text with the pipe character inserted
    Please Login or Register  to view this content.
    Copy both formulas across through Col_D

    Is that something you can work with?
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  3. #3
    Forum Guru
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,858

    Re: Insert character between lower case & upper case letters (ie. "VeryGood" -> "Very|Good

    In case anyone is interested in a VBA solution using regular expressions. This will replace multiple occurrences. I imagine there are more efficient ways to get the result but the following code was tested and working in Excel 2003.

    Please Login or Register  to view this content.
    Lewis

+ 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] Quick Question: Select Case to identify if first character of a cell is "6"
    By exlgh91 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-16-2013, 10:25 AM
  2. Case < > "Value", but Add "Value2" in the same case.. dude..
    By feroguz in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-18-2012, 07:57 PM
  3. VB Macro for Proper Case to Ignore "/" & "-"
    By jlcford in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-08-2008, 07:49 AM
  4. create links to check boxes marked "good" fair"and "bad"
    By pjb in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-19-2006, 09:25 PM
  5. excel defaults "i" to upper case
    By Joe in forum Excel General
    Replies: 1
    Last Post: 02-18-2005, 03:00 PM

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