How to extract values between different caracteres ?

[Inacio11]

Hi.
At link below I have attached a excel file where I am trying to extract the data number (day-month-year) from 3 different texts and with different lenghts, but with no sucess till now. I have included more details on excel file.
I need the formula (not VBA) !
https://www.sendspace.com/file/c6s2ji
Thanks in advance !
Inacio

 

[Michael M]

I'm sure there will be plenty of help available.....but....most posters will NOT download a file to look at your problem.
I'd suggest either describing your problem more clearly, or pasting some sample data directly to the forum

 

[Inacio11]

I'm sure there will be plenty of help available.....but....most posters will NOT download a file to look at your problem.
I'd suggest either describing your problem more clearly, or pasting some sample data directly to the forum

Hello Michael,
I thought that could be easier to clarify, but I understanding you. I will try:

A B | C | D
A1: SMS from: 88888 2018-07-02 10:59:27 >>>>> 2 | 7 |2018
A2: From 27300 July 02, 2018 at 0721PM>>>>>>>2 | 7 |2018
A3: From 11108 July 02, 2018 at 1017PM>>>>>>>2 | 7 |2018

As you can see above the text in each row is slightly different from each other, so I'd like to extract just the numbers related to the dates highlighted on red colour to columns in separate

Columns
A = full text
B= day
C= month
D= year
Regards,

 

[Inacio11]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[TD]Column C[/TD]
[TD]Column D[/TD]
[TD]Column E[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]day[/TD]
[TD]month[/TD]
[TD]year[/TD]
[/TR]
[TR]
[TD]SMS from: 88888 2018-07-02 10:59:27[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]7[/TD]
[TD]2018[/TD]
[/TR]
[TR]
[TD]From 27300 July 02, 2018 at 0721PM[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]7[/TD]
[TD]2018[/TD]
[/TR]
[TR]
[TD]From 11108 July 02, 2018 at 1017PM[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]7[/TD]
[TD]2018[/TD]
[/TR]
</tbody>[/TABLE]
Now it is better for understanding !!

 

[jtakw]

Hi,

This will accommodate either 5 or 6 digit Text addresses.

3 slightly different formulas for day, month, year, formulas copied down:

Book1
	C	D	E	F	G
4			day	month	year
5	SMS from: 88888 2018-07-02 10:59:27		2	7	2018
6	From 27300 July 02, 2018 at 0721PM		2	7	2018
7	From 11108 July 02, 2018 at 1017PM		2	7	2018
8	SMS from: 888889 2017-08-25 10:59:27		25	8	2017
9	From 273009 July 04, 2018 at 0721PM		4	7	2018
10	From 111089 January 31, 2015 at 1017PM		31	1	2015
Sheet119

Cell Formulas
Range	Formula
E5	=DAY(LEFT(SUBSTITUTE(TRIM(MID($C5,MIN(FIND({0,1,2,3,4,5,6,7,8,9},$C5&"0123456789"))+6,255))," ",REPT(" ",50),IF(ISNUMBER(FIND(",",$C5)),3,1)),50)+0)
F5	=MONTH(LEFT(SUBSTITUTE(TRIM(MID($C5,MIN(FIND({0,1,2,3,4,5,6,7,8,9},$C5&"0123456789"))+6,255))," ",REPT(" ",50),IF(ISNUMBER(FIND(",",$C5)),3,1)),50)+0)
G5	=YEAR(LEFT(SUBSTITUTE(TRIM(MID($C5,MIN(FIND({0,1,2,3,4,5,6,7,8,9},$C5&"0123456789"))+6,255))," ",REPT(" ",50),IF(ISNUMBER(FIND(",",$C5)),3,1)),50)+0)

 

[Inacio11]

Hi jtakw
First thanks for your early repply !
I did not understand yet, but the formulas works to text:

SMS from: 88888 2018-07-02 10:59:27

<tbody>
[TD="align: right"][/TD]

</tbody>

...but not for:
From 27300 July 02, 2018 at 0721PM
From 11108 July 02, 2018 at 1017PM

the result is #VALUE !

Inacio

 

[jtakw]

Have no idea what you mean, apparently it works for me as in my sample in Post #5 .

I've even just now tested on you Actual uploaded file at Sendspace, works fine.

You DO realize that there are 3 (three) Different formulas?

Using Post #5 for sample:

E5 formula for DAY, copied down.
F5 formula for MONTH, copied down.
G5 formula for YEAR, copied down.

 

[Inacio11]

Hi Jtakw,
Since then I've tried to run the formula, but it does not work for all rows (..and I have no idea why it works for you and not for me ?). Could you please check the excol n link: https://www.sendspace.com/file/vvn97m
Thanks in advance !
Inacio

 

[jtakw]

I've had a look at your new file and have No Idea why you have the #VALUE error in I6:K7...

If you select any of the Cells, say I6, click in the Formula Bar, hit Enter, the correct result Pops up.
Or, if you select I5, copy the formula and select I6:I7 and paste Formula, the correct results Pop up...

Don't know what you did there.

EDIT: Can you check and make sure you have Excel Calculations set to "Automatic"???

 

[Ingolf]

Hi jtakw
First thanks for your early repply !
I did not understand yet, but the formulas works to text:

SMS from: 88888 2018-07-02 10:59:27

<tbody>
[TD="align: right"][/TD]

</tbody>

...but not for:
From 27300 July 02, 2018 at 0721PM
From 11108 July 02, 2018 at 1017PM

the result is #VALUE !

Inacio

Hi,

It depends on the regional settings of the excel you are working on.

Try to change
From 27300 July 02, 2018 at 0721PM with
From 27300 02 July, 2018 at 0721PM and July with the name of the month in your country.

and the formulas will work.