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 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

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

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"GeorgeHutch" wrote:

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

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

"Stefi" wrote:

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 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

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

"ryguy7272" wrote:

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

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"GeorgeHutch" wrote:

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

Watch the spaces and returns...

=MID(A1,SEARCH("+",SUBSTITUTE(A1," ","+",LEN(A1)-
LEN(SUBSTITUTE(A1," ",""))-6))+1,256)


GeorgeHutch wrote:
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

"Stefi" wrote:

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 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

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:

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

"Stefi" wrote:

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 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