Solved

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

Posted on 2014-01-07
4
247 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:kcer333
  • 2
4 Comments
 
LVL 12

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 12

Accepted Solution

by:
Jeff Darling earned 500 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:Michael74
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 80

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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

706 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now