distinguish winning numbers from a given sentence

aaaaa34

Board Regular
Joined
Feb 25, 2014
Messages
62
Hey my friends,
In results page of loto results in one websites, winning numbers are placed in a sentenced with commas.
While i am copying them to excel, I want to have each number in cells along the row.
Sometimes while copying start of sentence, i don't copy every time from same point so formula should pay attention to it.
Can we distinguish these numbers and place them into seperate cells as it's shown in the worksheet?
Please have a look to the file. https://jmp.sh/sX6akuN
Thank you.


P.s: if i had to place this post under Formulas section, please sorry me. It can be moved.
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
In C1:

=TRIM(MID(SUBSTITUTE(SUBSTITUTE($A1,"ar",",ar"),",",REPT(" ",200)),COLUMNS($C1:C1)*200,200))

and confirm with Control+Shift+Enter. Drag down and across as needed.

The formula keys on the commas between numbers. To make it work right, make sure to include the comma before the first number, and the "are" after the last number.
 
Upvote 0
In C1:

=TRIM(MID(SUBSTITUTE(SUBSTITUTE($A1,"ar",",ar"),",",REPT(" ",200)),COLUMNS($C1:C1)*200,200))

and confirm with Control+Shift+Enter. Drag down and across as needed.

The formula keys on the commas between numbers. To make it work right, make sure to include the comma before the first number, and the "are" after the last number.

it gives error when i place the code into C1
 
Upvote 0
First off, you don't really need to use Control+Shift+Enter. Next, what error are you getting? It works for me:

ABCDEFGHIJKLMNOPQRSTUVWX

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]winner numbers , 1 , 2 , 3 , 21 , 27 , 29 , 30 , 42 , 43 , 45 , 51 , 52 , 53 , 54 , 55 , 63 , 64 , 71 , 72 , 73 , 74 , 78 are decided. [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]21[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]27[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]29[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]30[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]42[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]43[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]45[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]51[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]52[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]53[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]54[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]55[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]63[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]64[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]71[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]72[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]73[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]74[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]78[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]mbers , 3 , 4 , 9 , 10 , 11 , 16 , 17 , 19 , 24 , 26 , 29 , 30 , 31 , 40 , 41 , 49 , 52 , 53 , 55 , 62 , 65 , 77[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]9[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]10[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]11[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]16[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]17[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]19[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]24[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]26[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]29[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]30[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]31[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]40[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]41[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]49[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]52[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]53[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]55[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]62[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]65[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]77[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]rs , 2 , 3 , 11 , 13 , 18 , 19 , 26 , 29 , 34 , 38 , 43 , 44 , 51 , 53 , 54 , 59 , 60 , 64 , 65 , 69 , 72 , 79 are de[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]11[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]13[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]18[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]19[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]26[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]29[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]34[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]38[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]43[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]44[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]51[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]53[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]54[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]59[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]60[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]64[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]65[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]69[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]72[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]79[/TD]

</tbody>
Sheet1

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C1[/TH]
[TD="align: left"]=TRIM(MID(SUBSTITUTE(SUBSTITUTE($A1,"ar",",ar"),",",REPT(" ",200)),COLUMNS($C1:C1)*200,200))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



One possibility is that you have different regional settings. If so, you may need to change the commas in the formula to semicolons, like this:

=TRIM(MID(SUBSTITUTE(SUBSTITUTE($A1;"ar";",ar");",";REPT(" ";200));COLUMNS($C1:C1)*200;200))

The 2 commas left are in quotes and refer to the commas in your text.
 
Upvote 0
First off, you don't really need to use Control+Shift+Enter. Next, what error are you getting? It works for me.
One possibility is that you have different regional settings. If so, you may need to change the commas in the formula to semicolons, like this:
=TRIM(MID(SUBSTITUTE(SUBSTITUTE($A1;"ar";",ar");",";REPT(" ";200));COLUMNS($C1:C1)*200;200))
The 2 commas left are in quotes and refer to the commas in your text.
yessss!!! its totally linked to my regional settings as you indicated. second code worked and ctrl+shift+enter not needed.
super code Eric, muchas gracias
 
Upvote 0
A couple of other alternative you may wish to consider. (Don't forget any comma/semicolon replacements required)

A) Formulas
C1 copied down
D1 copied across and down

Excel Workbook
ABCDEFGHIJKLMNOPQRSTUVWX
1winner numbers , 1 , 2 , 3 , 21 , 27 , 29 , 30 , 42 , 43 , 45 , 51 , 52 , 53 , 54 , 55 , 63 , 64 , 71 , 72 , 73 , 74 , 78 are decided.12321272930424345515253545563647172737478
2mbers , 3 , 4 , 9 , 10 , 11 , 16 , 17 , 19 , 24 , 26 , 29 , 30 , 31 , 40 , 41 , 49 , 52 , 53 , 55 , 62 , 65 , 7734910111617192426293031404149525355626577
3rs , 2 , 3 , 11 , 13 , 18 , 19 , 26 , 29 , 34 , 38 , 43 , 44 , 51 , 53 , 54 , 59 , 60 , 64 , 65 , 69 , 72 , 79 are de231113181926293438434451535459606465697279
Lotto numbers



B) A User-defined function.
Pretty short code and a single simple worksheet formula. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy across and down.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Code:
Function Num(s As String, n As Long) As Long
  Num = Val(Split(s, ",")(n))
End Function

Excel Workbook
ABCDEFGHIJKLMNOPQRSTUVWX
1winner numbers , 1 , 2 , 3 , 21 , 27 , 29 , 30 , 42 , 43 , 45 , 51 , 52 , 53 , 54 , 55 , 63 , 64 , 71 , 72 , 73 , 74 , 78 are decided.12321272930424345515253545563647172737478
2mbers , 3 , 4 , 9 , 10 , 11 , 16 , 17 , 19 , 24 , 26 , 29 , 30 , 31 , 40 , 41 , 49 , 52 , 53 , 55 , 62 , 65 , 7734910111617192426293031404149525355626577
3rs , 2 , 3 , 11 , 13 , 18 , 19 , 26 , 29 , 34 , 38 , 43 , 44 , 51 , 53 , 54 , 59 , 60 , 64 , 65 , 69 , 72 , 79 are de231113181926293438434451535459606465697279
Lotto numbers (2)
 
Upvote 0
Spreadsheet Formulas
CellFormula
C1=MID(A1,FIND(",",A1)+2,2)+0
D1=MID($A1,FIND(" "&IF(C1="",",",C1)&" ",$A1)+4+LEN(C1),2)+0

<tbody>
</tbody>

<tbody>
</tbody>
code worked very good in the beginning but failed in next rows. Please check the file. https://jmp.sh/Dx5wJai
and can't you share with me that VBA code in a file and I can dowload it from ur link please?
 
Last edited:
Upvote 0
code worked very good in the beginning but failed in next rows.
That is because your original sample data was not quite representative of your real data. In your earlier sample data there was no numbers other than the ones to be extracted. In your actual data there are quite a few other numbers in the data

Try these instead.

Excel Workbook
BCDEFGHIJKLMNOPQRSTUVWXY
1Milli Piyango Idaresi tarafindan dzenlenen On Numara 17 Mart 2014 tarihindeki 606.hafta ekilis sonularina gre 'On Numara' sans oyununun bu haftaki ekilisinde kazanan numaralar , 3 , 6 , 7 , 9 , 15 , 19 , 29 , 32 , 35 , 37 , 38 , 39 , 41 , 42 , 43 , 44 , 50 , 54 , 57 , 62 , 70 , 78 olarak belirlendi.*3679151929323537383941424344505457627078
Sheet1 (3)




and can't you share with me that VBA code in a file and I can dowload it from ur link please?
Sorry, I see no need for that. It is just 3 lines of code to copy from the forum and I have given step-by-step instructions of what to do.
 
Upvote 0
Spreadsheet Formulas
CellFormula
D1=MID(B1,FIND(" , ",B1)+3,2)+0
E1=MID($B1,FIND(", "&D1&" ,",$B1)+5+LEN(D1),2)+0

<tbody>
</tbody>

<tbody>
</tbody>
Sorry, I see no need for that. It is just 3 lines of code to copy from the forum and I have given step-by-step instructions of what to do.
Super Peter Pan!!! muchas gracias amigo!!! thanks a lot!!! :beerchug:
oke i will insert vba code by myself and will try it too.
 
Upvote 0

Forum statistics

Threads
1,223,900
Messages
6,175,276
Members
452,629
Latest member
SahilPolekar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top