Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Excel vba - need to strip off 7 words from the right in a cell.

Posted on 2014-01-07
4
Medium Priority
?
262 Views
Last Modified: 2014-01-08
Trying to automate spreadsheet with vba-

 Normal cell: 005 Mech Room 1 5.0 Jul 1900 242 4.9 1067.0 0.23
Desired cell: 005 Mech Room 1

I need a routine to more or less count spaces or words from the right and then trim off?
The data is consistent. 7 words or spaces from the right in each cell. The character count can vary so I can't just trim. But the word count is consistent (from right only).
0
Comment
Question by:Keith Conaway
  • 2
4 Comments
 
LVL 13

Expert Comment

by:Jeff Darling
ID: 39763560
Here is a function that produces the output using split.

Public Function parsetest(strInput As String, iWords As Integer) As String

Dim strInputs() As String
Dim strOutput As String

strInputs = Split(strInput, " ")

For i = 0 To iWords - 1

strOutput = strOutput & strInputs(i) & " "

Next i

parsetest = Trim(strOutput)


End Function

Open in new window

ParseTest.xlsm
0
 
LVL 13

Accepted Solution

by:
Jeff Darling earned 2000 total points
ID: 39763578
oops, I think I misunderstood your specs, you wanted 7 from the right.  

This code assumes you always have at least 8 words....  it will break if it doesn't.

Public Function parsetest2(strInput As String) As String

Dim strInputs() As String
Dim strOutput As String

strInputs = Split(strInput, " ")

For i = 0 To UBound(strInputs) - 7

strOutput = strOutput & strInputs(i) & " "

Next i

parsetest2 = Trim(strOutput)



End Function

Open in new window

ParseTest.xlsm
0
 
LVL 23

Expert Comment

by:Michael Fowler
ID: 39763592
This should work
Sub test()
   Dim arr() As String, i As Integer
   arr = split(Range("A7").Value, " ")
   For i = 0 To UBound(arr) - 7
      Range("B8").Value = Range("B8").Value & arr(i) & " "
   Next
End Sub

Open in new window


Michael
0
 
LVL 81

Expert Comment

by:byundt
ID: 39763974
Here is a macro that will work on a selected range of cells, with any number of rows and/or columns. If the cell contains more than 7 spaces, it will be truncated to delete the last 7 spaces plus all text to the right of them. The macro uses array transfer for speed. Results will be placed in the original cells.

As written, the macro ignores cells that are blank, contain less than 7 spaces, contain error values or are numbers. If the user inadvertently selects an entire column, the macro only processes those cells that are within the used range.
Sub DeleteLast7Words()
Dim rg As Range
Dim v As Variant
Dim i As Long, j As Long, k As Long
Set rg = Intersect(ActiveSheet.UsedRange, Selection.Cells)
If Not rg Is Nothing Then
    On Error Resume Next
    v = rg.Value
    For i = rg.Rows.Count To 1 Step -1
        For j = rg.Columns.Count To 1 Step -1
            k = 0
            k = Len(v(i, j)) - Len(Replace(v(i, j), " ", ""))
            If k > 7 Then v(i, j) = Left(v(i, j), InStr(1, Application.Substitute(v(i, j), " ", "|", k - 7), "|") - 1)
        Next
    Next
    rg.Value = v
    On Error GoTo 0
End If
End Sub

Open in new window

0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

885 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question