Separating Text

[OversizedCranium]

Hello,

I've recently been given an awful report that comes out

COL 1 COL 2
NAME String (includes Name (age) and continues string for others in similar fashion)

Example "Joe Bloggs Brother (age: 3) Joe Bloggs (age: 8) "

All I'm looking to extract is the values of age. Preferably separate.

I don't have the text sep function and I'm driving myself up the wall using left and right, or even Find & Replace functions.

If anyone has a solution would be much appreciated

 

[rpaulson]

data menu
then - text to columns
play around with the delimiter till you get something close to chat you want

 

[Fluff]

How about

Fluff.xlsm
	A	B	C
1
2	Joe Bloggs Brother (age: 3) Joe Bloggs (age: 8)	3	8
Data

Cell Formulas
Range	Formula
B2:C2	B2	=TRANSPOSE(FILTERXML("<k><m>"&SUBSTITUTE(SUBSTITUTE(A2,")", " ")," ", "</m><m>")&"</m></k>","//m[.=number()]"))
Dynamic array formulas.

 

[NdNoviceHlp]

Here's a VBA solution. Not quite clear where your data is and/or where and in what format you want your output in? So, input in Sheet1 "A1" to "A" & whatever and output to "B". HTH. Dave

Private Sub Test()
Dim LastRow As Integer, Cnt As Integer, Cnt2 As Integer
Dim OldStr As String, NewStr As String
'names in sheet1 "A1" to A & whatever
With Sheets("Sheet1")
LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
'loop rows
For Cnt = 1 To LastRow
NewStr = vbNullString
OldStr = CStr(.Range("A" & Cnt).Value)
'loop through characters in cell
For Cnt2 = 1 To Len(OldStr)
'if character numeric add to output NewStr
If IsNumeric(Mid(OldStr, Cnt2, 1)) Then
NewStr = NewStr & "," & Mid(OldStr, Cnt2, 1)
End If
Next Cnt2
'remove leading apostrophe
NewStr = Right(NewStr, Len(NewStr) - 1)
'output to adjacent column "B"
.Range("A" & Cnt).Offset(0, 1) = NewStr
Next Cnt
End With
End Sub

 

[NdNoviceHlp]

Trial 1 was a bit short sited and didn't allow for multiple digits or no numbers in the data. Here's trial 2 in splendid VBA format. Dave

Private Sub Test()
Dim LastRow As Integer, Cnt As Integer, Cnt2 As Integer
Dim OldStr As String, NewStr As String, TStr As String
'names in sheet1 "A1" to A & whatever
With Sheets("Sheet1")
LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
'loop rows
For Cnt = 1 To LastRow
NewStr = vbNullString
OldStr = CStr(.Range("A" & Cnt).Value)
'loop through characters in cell
For Cnt2 = 1 To Len(OldStr)
'if character numeric add to tempStr
If IsNumeric(Mid(OldStr, Cnt2, 1)) Then
TStr = TStr & Mid(OldStr, Cnt2, 1)
Else 'not numeric
'previous numbers collected/add to output NewStr
If TStr <> vbNullString Then
NewStr = NewStr & "," & TStr
TStr = vbNullString
End If
End If
Next Cnt2
'no numbers in data
If NewStr <> vbNullString Then
'remove leading apostrophe
NewStr = Right(NewStr, Len(NewStr) - 1)
'output to adjacent column "B"
.Range("A" & Cnt).Offset(0, 1) = NewStr
End If
Next Cnt
End With
End Sub