Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Dec 2001
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    reverse cell contents (excell/97-2000)

    I need to reverse the contents of all filled cells in column 1
    ie- Cell a1 contains "edcba hgf" that should read "abcde fgh"----the problem arises from importing from foreign language.
    Letters spaces and number in cells are all allowed.
    I have nearly got there but my brain has now gone.....
    Thanx
    Smbs

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: reverse cell contents (excell/97-2000)

    You can do it cell by cell with a function [ eg. =ReverseText(a1)]

    <pre>Function ReverseText(rng As Range) As String
    Dim sWord As String
    Dim x As Integer

    ReverseText = ""
    sWord = rng.Value
    For x = 1 To Len(sWord)
    ReverseText = Mid(sWord, x, 1) & ReverseText
    Next x

    End Function
    </pre>


    Or you can select a range of cells and do it in the cell:
    <pre>Sub sReverseText()
    Dim sWord As String
    Dim myCell As Range
    Dim x As Integer
    Dim sReverse As String

    For Each myCell In Selection
    sReverse = ""
    sWord = myCell.Value
    For x = 1 To Len(sWord)
    sReverse = Mid(sWord, x, 1) & sReverse
    Next x
    myCell.Value = sReverse
    Next

    End Sub
    </pre>


    Steve

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: reverse cell contents (excell/97-2000)

    If I understand what you are asking correctly, the following VBA code will do what you ask for all of the cells in the current selection.

    <pre>Public Sub RevWords()
    Dim oCell As Range
    Dim strW1 As String, strW2 As String, strW3 As String
    Dim I As Long
    For Each oCell In Selection
    strW3 = ""
    strW1 = oCell.Value & " "
    Do While Len(strW1) > 1
    strW2 = Left(strW1, InStr(strW1, " ") - 1)
    strW1 = Trim(Right(strW1, Len(strW1) - InStr(strW1, " "))) & " "
    For I = Len(strW2) To 1 Step -1
    strW3 = strW3 & Mid(strW2, I, 1)
    Next I
    strW3 = strW3 & " "
    Loop
    oCell.Value = Trim(strW3)
    Next oCell
    End Sub
    </pre>

    Legare Coleman

  4. #4
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: reverse cell contents (excell/97-2000)

    <hr>Cell a1 contains "edcba hgf" that should read "abcde fgh"----<hr>

    Do you really want "abcde fgh" or do you want "fgh abcde" if the latter then you could use something like

    Worksheets("sheet1").Range("b1") = StrReverse(Worksheets("sheet1").Range("a1"))

    If you want to reverse just the letters in each word with out altering the word order then that will be more fun to work out <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    Peter

  5. #5
    2 Star Lounger
    Join Date
    Dec 2001
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: reverse cell contents (excell/97-2000)

    Thanx -for quick answers-- all worked just fine!
    Smbs

Posting Permissions

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