Extracting numbers and dates from Text String

Question

Here is a sample of a text string that appears multiple times in my worksheet -

86 SUPPORT EMAILS @ $1.00 57 SUPPORT CALLS @ $3.00 DEC 11 THUR DEC 15

I am trying to extract the number of Support Emails in a separate field, with the date in another field AND

The number of Support Calls, with the date in another field.  I don't need the dollar amounts because they are consistent.

I am having a problem writing a formula and I am not proficient enough to use VBA.  I am running Excel 2016 on a Mac.

Any guidance or assistance is appreciated.

Thank you

Elliot

Answer 1

Hi,

In cell F3 and F4, I typed 86 and 75.  I then selected F3:F8 and pressed Ctrl+E (Data > Flash Fill).  Do the same in column G and H as well.

Answer 2

Here is an example of the data in the worksheet -

Invoice	Invoice	Invoice	Amount	Comment/Reasons
Date	Type	Number	Emails	Calls	Date Range
1/5/18	SUPPORT CALLS/E-MAILS	1282903	$257.00	86 SUPPORT EMAILS @ $1.00 57 SUPPORT CALLS @ $3.00 DEC 11 THUR DEC 15	86	57	DEC 11 THRU DEC 15
1/5/18	SUPPORT CALLS/E-MAILS	1282904	$261.00	75 SUPPORT EMAILS @ $1.00 62 SUPPORT CALLS @ $3.00 DEC 18 THRU DEC 22	75	62	DEC 18 THRU DEC 22
1/5/18	SUPPORT CALLS/E-MAILS	1282905	$85.00	34 SUPPORT EMAILS @ $1.00 17 SUPPORT CALLS @ $3.00 DEC 25 THRU DEC 29	34	17	DEC 25 THRU DEC 29
1/22/18	SUPPORT CALLS/E-MAILS	1283988	$115.00	55 SUPPORT EMAILS @ $1.00 20 SUPPORT CALLS @ $3.00 JAN 1 THRU JAN 5
1/22/18	SUPPORT CALLS/E-MAILS	1283989	$164.00	71 SUPPORT EMAILS @ $1.00 31 SUPPORT CALLS @ $3.00 JAN 8 THRU JAN 12
1/22/18	SUPPORT CALLS/E-MAILS	1283990	$144.00	51 SUPPORT EMAIL @ $1.00 31 SUPPORT CALLS @ $ 3.00 JAN 15 THRU JAN 19
2/1/18	Finance Charge	0	$0.94
2/2/18	Finance Charge	0	$0.95
2/3/18	Finance Charge	0	$0.95
2/4/18	Finance Charge	0	$1.28
2/5/18	Finance Charge	0	$1.29
2/5/18	SUPPORT CALLS/E-MAILS	1284970	$113.00	59 SUPPORT EMAILS @ $1.00 18 SUPPORT CALLS @ $3.00 JAN 29 THRU 2-2
				The email, calls and Date Range columns are currently manually entered.  I'm looking for a way that could <br><br><br>automate this.<br><br>Thank you

Answer 3

Hi,

I cannot comment unless I see a few examples with your expected results.

Answer 4

Hi,

If the structure remains the same, then Data > Flash Fill should work.  If the structure does not remain the same then share some more  line items so that we can identify some pattern in your data and accordingly suggest a solution.

The data stays the same for those fields, but the column that contains that information is an "information" column and occasionally there is other data in those fields but the other data is not needed, it is usually ignored or thrown away.

I hope that is what you are asking.

Thank you

Elliot

Answer 5

Hi,

If the structure remains the same, then Data > Flash Fill should work.  If the structure does not remain the same then share some more  line items so that we can identify some pattern in your data and accordingly suggest a solution.