VBA, excel cut a part from a string

Question

I would like to get a part from a string. Im not familiar with VBA or unusual excel functions, but I think maybe a macro or a VBA code can help me.

I need to cut down everything from the whole string from the cell, except what is after the Total. To clear this, I need the numbers which stand after the total. I dont know any functions in excel which can solve my problem. Is there something like trim or something which recognize the 'total' and give me the number after that?

Example: From this cell I need the 47 and the other 47

Cell: Appvg - Total: 47 GB - Free:20 Gb; rootvg - Total: 47 -Free: <1 GB>

Will you only be looking for the numbers after Total? Or might you be using some other key words also? — Ron Rosenfeld
– Ron Rosenfeld, Commented May 6, 2015 at 19:51

BruceWayne · Accepted Answer · 2015-05-06 19:28:37Z

Will the two Totals always be the same? If so, you can use this formula (just put this in a cell on the same row as your data, I am assuming your Cell is A1):

=TRIM(MID(A2,SEARCH("total: ",A2)+LEN("total: "),2))

If the GB numbers will ever be larger than two digits, change the "2" to "3". Do you have any other example cell data to work off to improve the above?

Edit: If the numbers will be different, you can use this: =MID(A1,SEARCH("appvg - total: ",A1)+LEN("appvg - total: "),2)&", "&MID(A1,SEARCH("rootvg - total: ",A1)+LEN("rootvg - total: "),2)

edit 2: I am working on the formula so if the GB are greater than two digits, such as 964 GB, to have a way to dynamically pick that out. This seems to work up to numbers 4 digits long: =MID(A4,SEARCH("appvg - total: ",A4)+LEN("appvg - total: "),SEARCH(" G",A4)-SEARCH(":",A4)-2)&", "&MID(A4,SEARCH("rootvg - total: ",A4)+LEN("rootvg - total: "),SEARCH(" G",A4,SEARCH(" G",A4)-SEARCH(":",A4)-1)-SEARCH(":",A4)-2)

rryanp · Accepted Answer · 2015-05-06 20:10:36Z

0

If you want a true VBA solution, here's a function to get to at least the first number (and a similar one can be created to get to the second number):

Function first_number(a_string As String) As Double
    'assumes one space after each "Total:" occurrence
    loc_of_first_total = InStr(1, a_string, "Total")
    end_of_first_number = InStr(loc_of_first_total + 7, a_string, " ") - 1
    first_number = CDbl(Mid(a_string, loc_of_first_total + 7, end_of_first_number - loc_of_first_total - 6))
End Function

If you're not familiar with InStr, a Google search would probably get to a better explanation than I could provide. All the numbers I used (e.g., 7, 1, 6) are just the math of where the number will be if you assume that "Total:" plus one space exists before each number needed. I wasn't sure if decimals could exist, so I returned a Double, but it could be an Integer or Long depending on what you expect in the data.

answered May 6, 2015 at 20:10

rryanp

1,0278 gold badges26 silver badges45 bronze badges

1 Comment

rryanp Over a year ago

I should note though that I usually always try to do a formula solution when possible, so I prefer @user3578951's answer. But if you need something quick-and-dirty, or something that can handle longer numbers and/or decimals, this could be a starting point.

Ron Rosenfeld · Accepted Answer · 2015-05-06 20:21:12Z

0

For the first instance: (where WordToFind might be "Total:"

=LOOKUP(2,1/MID(A1,SEARCH(WordToFind,A1)+LEN(WordToFind)+1,ROW(INDIRECT("1:5"))),MID(A1,SEARCH(WordToFind,A1)+LEN(WordToFind)+1,ROW(INDIRECT("1:5"))))

For the 2nd instance:

=LOOKUP(2,1/MID(A1,SEARCH(WordToFind,A1,SEARCH(WordToFind,A1)+1)+LEN(WordToFind)+1,ROW(INDIRECT("1:5"))),MID(A1,SEARCH(WordToFind,A1,SEARCH(WordToFind,A1)+1)+LEN(WordToFind)+1,ROW(INDIRECT("1:5"))))

If you need more instances, VBA might be a better option. If your number portion might be longer than 5 characters, change the "5" in "1:5"

answered May 6, 2015 at 20:21

Ron Rosenfeld

62k7 gold badges35 silver badges71 bronze badges

Comments

EEM · Accepted Answer · 2015-05-20 16:01:18Z

I assume that you need the results in two separated cells. Also note that the key string to search for is "Total:"

Cell A11 has the following value (use different totals as to validate the different results)

Appvg - Total: 147 GB - Free:20 Gb; rootvg - Total: 247 -Free: <1 GB>

Use this formula to obtain the Total: 147 (GB)

=LEFT(SUBSTITUTE(A11,LEFT(A11,LEN("Total:")+SEARCH("Total:",A11)),""),
-1+SEARCH(" ",SUBSTITUTE(A11,LEFT(A11,LEN("Total:")+SEARCH("Total:",A11)),"")))

Use this formula to obtain the Total: 247 (-Free)

=LEFT(SUBSTITUTE(A11,LEFT(A11,
LEN("Total:")+SEARCH("|",SUBSTITUTE(A11,"Total:","|",2))),""),
-1+SEARCH(" ",SUBSTITUTE(A11,
LEFT(A11,LEN("Total:")+SEARCH("|",SUBSTITUTE(A11,"Total:","|",2))),"")))

In case the key string changes, a cell can be use to enter the key strings to search. If this is the case use these formulas:

The key string "Total:" is entered in cell A12

Use this formula to obtain the Total: 147 (GB)

=LEFT(SUBSTITUTE(A11,LEFT(A11,LEN(A12)+SEARCH(A12,A11)),""),
-1+SEARCH(" ",SUBSTITUTE(A11,LEFT(A11,LEN(A12)+SEARCH(A12,A11)),"")))

Use this formula to obtain the Total: 247 (-Free)

=LEFT(SUBSTITUTE(A11,LEFT(A11,
LEN(A12)+SEARCH("|",SUBSTITUTE(A11,A12,"|",2))),""),
-1+SEARCH(" ",SUBSTITUTE(A11,LEFT(A11,
LEN(A12)+SEARCH("|",SUBSTITUTE(A11,A12,"|",2))),"")))

All formulas work regardless of the length of the numbers

Collectives™ on Stack Overflow

VBA, excel cut a part from a string

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related