Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 17

Thread: Text replacement

  1. #1
    New Member
    Join Date
    May 2011
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Text replacement

    I have a text in an Excel worksheet that contains "1a" and "3b" and i want to change the letters to uppercase with out changing the rest of the text case

    Cheers Clive

  2. #2
    Board Regular
    Join Date
    Apr 2010
    Posts
    10,339
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Text replacement

    find 1a and replace with 1A - or is it more complicated ?

  3. #3
    New Member
    Join Date
    May 2011
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Text replacement

    Quote Originally Posted by oldbrewer View Post
    find 1a and replace with 1A - or is it more complicated ?
    Yes I have a lot of text that contain a lot of this type 1a 3c 2f
    etc, (they can be any number), and of I convert the text to proper case they are still lowercase, so I need something to find these and change the case. I have tried converting all to upper case and back to proper case but that does not work. Any ideas would be great.

    Clive
    Last edited by ceb; Feb 20th, 2018 at 06:40 AM.

  4. #4
    New Member
    Join Date
    Feb 2018
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Text replacement

    Have you tried:
    Code:
    =UPPER(cell)
    VBA version:
    Code:
        ActiveCell.FormulaR1C1 = "=UPPER(RC[-1])"
    Last edited by Daminc2; Feb 20th, 2018 at 07:49 AM. Reason: addition

  5. #5
    New Member
    Join Date
    Feb 2018
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Text replacement

    Have a look at this bit of code too (I was helped out with this...)
    Code:
    Range("D" & RowValue).Select
            ActiveCell.FormulaR1C1 = "=MID(R[0]C[-1],SEARCH(""Band"",R[0]C[-1])+5,1)"

  6. #6
    New Member
    Join Date
    May 2011
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Text replacement

    Upper just turns everything to uppercase which I don't want I just want to search for a number followed by a character an make the character uppercase.
    i.e 1a, 5g, 9f = 1A, 5G and 9F. The rest of the text is in the correct case.

    I could not get the last post code to work just stopped with an error on .Select

    Clive

  7. #7
    New Member
    Join Date
    Feb 2018
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Text replacement

    Sorry. I posted a clip from a macro that I use in order to highlight the use of MID and SEARCH in context that might of helped you isolate the items you wanted. I don't know if the bits you want occur at the same place within a cell or if you have to search for incidences etc.

  8. #8
    New Member
    Join Date
    Feb 2018
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Text replacement

    This was the full clip of the macro:
    Code:
        For Each rrow In rowRange
        RowValue = rrow.Row
        
            Range("D" & RowValue).Select
            ActiveCell.FormulaR1C1 = "=MID(R[0]C[-1],SEARCH(""Band"",R[0]C[-1])+5,1)"
        Next rrow
    It looped through cells looking for the word "Band" and selected the number that was just after that word.
    e.g Band 6, showed the number 6 etc.

  9. #9
    New Member
    Join Date
    Feb 2018
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Text replacement

    I also found this solution which is a bit beyond my expertise:
    Code:
    {=MID($A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},$A1&"0123456789")),MAX(IF(ISNUMBER(VALUE(MID($A1,ROW(INDIRECT("1:" & LEN($A1))),1))),ROW(INDIRECT("1:" & LEN($A1)))))-MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},$A1&"0123456789"))+1)}

  10. #10
    New Member
    Join Date
    May 2011
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Text replacement

    Thank you for your help Daminc2.
    But I am not trying to extract numbers from my text I am just trying to change the case of the character following the number. See example text below:

    "Comp 5c Train 2 Stage 1 Flocculator No.3 Simocode
    Comp 6a Train 2 Stage 2 Flocculator No.1 Variable Speed Drive
    Comp 6a Train 2 Stage 2 Flocculator No.1 Simocode
    Comp 6b Train 2 Stage 2 Flocculator No.2 Variable Speed Drive
    Comp 6b Train 2 Stage 2 Flocculator No.2 Simocode
    Comp 6c Train 2 Stage 2 Flocculator No.3 Variable Speed Drive
    Comp 6c Train 2 Stage 2 Flocculator No.3 Simocode"

    As you can see the letters after the numbers are lower case I want something to search for them and set them to uppercase.

    Clive
    Last edited by ceb; Feb 20th, 2018 at 10:26 AM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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