Solved

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

Posted on 2014-01-07
4
250 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 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
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 …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

910 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

21 Experts available now in Live!

Get 1:1 Help Now