Splitting out numerics between bracketed text 2

[smooveb]

Hi everyone,

this is a continuation of a previous post. I have the following

BA DD
Unix Admin[38%]
DBA [40%]

What I'd like to do is get the 38 and the 40 into column B, and removed from column A. I could do this through the use of some ugly code:

1. find length of total
2. find number of chars to first bracket
3. return rightmost character difference to get right 2 or three chars
4. and on and on until I finally have what I'm after...

But I'm wondering if there's a more efficient way anyone can think of?

Thanks in advance!
Barrett

 

[VBAjedi]

If it's a one-shot deal, you should be able to select the column, then use Text to Columns and specify "[" as your column seperator. Then just do a Search/Replace on the second column to replace "]" with nothing.

Otherwise you may find it easier to write the code you described (which could really just be a modification of the code I gave you in the other post). If you're interested in this route, post back and I'll describe one way you could go about it.

VBAjedi

 

[smooveb]

Hey Jedi ~

well, it's going to loop through a number of rows, created by the macro you helped me create before. Now I need to loop through those, and extract the numerics for purposes of aggregation and statistical analysis.

Is there a way to do this using Split? Otherwise I imagine doing a 1. len 2. search 3. right 4. len 5. search 6. left type of interaction with each cell.

Thanks,
Barrett

 

[VBAjedi]

Well, if you decided to use my parsing code, you could just add another dimension to the array, then check for the bracketed numbers each time you find the end of a segment. Something like:

Dim ArrValues(100, 2) As String
A = Range("A13").Value
y = 1
For x = 1 To Len(A)
   B = Mid(A, x, 1)
   If B = "," Then
      z = InStr(1, ArrValues(y, 1), "[")
      If z > 0 Then
         ArrValues(y, 2) = Mid(ArrValues(y, 1), (z + 1))
         ArrValues(y, 1) = Left(ArrValues(y, 1), (z - 1))
      End If
      y = y + 1
   Else
      ArrValues(y, 1) = ArrValues(y, 1) & B
   End If
Next x
End Sub

Then you just write ArrValues(x,1) to column a, and ArrValues(x,2) to column b. I didn't bother to add code to strip off the trailing bracket ("]"), but you could easily do that.

Does that make sense?

VBAjedi

 

[PHV]

You may try something like this:
a = "Unix Admin[38%]"
If InStr(a, "[") > 0 Then
a = Replace(Split(a, "[")(1), "%]", "")
End If

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[TonyJollans]

You can use Val to drop the trailing non-numerics ..

[blue]

[B2] = Val(Mid([A2], InStr([A2], "[") + 1))

[/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[VBAjedi]

Trick, Tony! It ain't even my question, but have a star!



VBAjedi

 

[TonyJollans]

Thanks, jedi. It's a handy little function - it has its quirks, but don't they all?

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[smooveb]

Once again, you guys are awesome!