Separating data

  • Thread starter Thread starter GeorgeHutch
  • Start date Start date
G

GeorgeHutch

I am currently sorting through endless data wich i only need a small part of
it.

the data looks like this below but 100 time as long and in a text file.

CT Stage 1 157 40.80 157 97.45 40.53 15 4.00
CT Stage 2 148 20.67 152 94.08 21.17 0 0.00
CT Walkround 19 7.80 19 84.21 7.80 0 0.00
CT 1 Month Review 52 1.00 51 100.00 0.93 3 0.07
CT 2 Month Review 21 2.20 21 100.00 2.20 0 0.00
CG Certs 665 108.06 656 100.00 106.60 284 46.15
Issue CG Certs 543 9.05 543 100.00 9.05 0 0.00
Send Deed Pack / DOA 120 26.00 117 100.00 25.35 15 3.25

Taking the top one as an example i only need the end data (157 40.80 157
97.45 40.53 15 4.00) but when i try to text to collumns i cannot get it right
and it is taking me an age to sort the data right.

any ideas?

thanks

George
 
If your strings are in column A then insert this formula in B1:
=MID(A1,SEARCH("+",SUBSTITUTE(A1," ","+",LEN(A1)-LEN(SUBSTITUTE(A1,"
",""))-6))+1,256)

fill it down, copy column B, PasteSpecial/Values to column C, now you can
split up the remaining strings in column C with Text to column (space as
separator), provided that all strings contain 7 numeric values.

--
Regards!
Stefi



„GeorgeHutch†ezt írta:
 
I understand your desire to strip out only numbers, but things like Stage 1
and Stage 2, may trip you up a bit. Using the function below, 1 & 2 are
pulled out, just like 157 & 148. Anyway, try this and see if you can get
basically what you want:

Function Numbers(Cell As String) As String
Cell = WorksheetFunction.Substitute(Cell, "/", " ")
Dim StrArray As Variant, i As Long
StrArray = Split(Cell)
For i = 0 To UBound(StrArray)
If IsNumeric(StrArray(i)) Then
Numbers = Numbers & StrArray(i) & ", "
End If
Next i
If Len(Numbers) > 2 Then
Numbers = Left(Numbers, Len(Numbers) - 2)
End If
End Function
 
Hi Stefi,

I have tried this about 10 times but it is keeps telling me there is an
error in the fomula and returns a #Value!

could you confirm please?

thanks

George
 
Hi Ryan,

thanks for sending this, but im not too sure were i should go for this.

Were abouts do i paste this and i will give it a go?

thanks

George
 
Watch the spaces and returns...

=MID(A1,SEARCH("+",SUBSTITUTE(A1," ","+",LEN(A1)-
LEN(SUBSTITUTE(A1," ",""))-6))+1,256)
 
I checked again and it works for me! Check again
- your argument separator (maybe it's not comma)
- spaces in this part of the formula like Glenn suggested:

SUBSTITUTE(A1,"_","+",LEN(A1)-LEN(SUBSTITUTE(A1,"_",""))-6))

You have to put one space (" ") instead of underscores ("_")!

--
Regards!
Stefi



„GeorgeHutch†ezt írta:
 
Back
Top