Need help changing one column of data into two

[Howard]

I have a column in Excel with data like this:

9/4/57
9/3/57
1957-09-02 (2)
8/23/57
8/23/57
8/17/57
8/16/57
1957-08-13 (2)
1957-08-13 (2)
1957-08-02 (1)
1957-07-28 (2)

In BBEdit, I would like to extract all the numbers in parentheses -- (1) and (2) -- and 

put them in a second column without the parentheses. Then, I would like to convert all the dates in Column 1 that are in yyyy-mm-dd format to mm/dd/yy format so that I can copy the BBEdit result and paste it into Excel as two columns (See Sample Output). 

Sample Output

Column 1       Column 2

9/4/57
9/3/57
1957-09-02     2

How can I do this in BBEdit?

Howard

[Howard]

Correction to Sample Output.

It should look like this:

Sample Output

Column 1       Column 2

9/4/57
9/3/57
9/2/57            2

[Darren Duncan]

Howard,

This should be easy to accomplish with search and replace regular expressions.

If both of the things you want to change always appear together such as the
longer format dates plus the counts, then you can change both in a single
expression and you can leave the other lines with the short dates and no numbers
alone, as those would already import correctly, assuming the date column is first.

Search for this grep pattern:

^\s*19(\d\d)-(\d\d)-(\d\d)\s+\(\d+\)\s*$

Replace with:

\2/\3/\1\t\4

This pattern is more strict and matches a whole line with optional
leading/trailing whitespace, and it leaves alone dates that don't start with 19
so if it skips any then you know those are a different century; otherwise
replace the 19 with \d\d if you don't care about that.

The result has a tab between the 2 columns which is how in copy/paste Excel
would know the boundaries.

-- Darren Duncan

On 2022-05-31 7:09 a.m., Howard wrote:
> Correction to *Sample Output*.

>
> It should look like this:
>

> *Sample Output*
> *
> *
> *Column 1       Column 2*

> 9/4/57
> 9/3/57
> 9/2/57            2
>
> On Tuesday, 31 May 2022 at 9:54:59 am UTC-4 Howard wrote:
>
> I have a column in Excel with data like this:
>
> 9/4/57
> 9/3/57
> 1957-09-02 (2)
> 8/23/57
> 8/23/57
> 8/17/57
> 8/16/57
> 1957-08-13 (2)
> 1957-08-13 (2)
> 1957-08-02 (1)
> 1957-07-28 (2)
>
> In BBEdit, I would like to extract all the numbers in parentheses -- (1) and
> (2) -- and
> put them in a second column without the parentheses. Then, I would like to

> convert all the dates in Column 1 that are in *yyyy-mm-dd* format to
> *mm/dd/yy* format so that I can copy the BBEdit result and paste it into
> Excel as two columns (See *Sample Output*).
>
> *Sample Output*
> *
> *
> *Column 1       Column 2*
> 9/4/57
> 9/3/57
> 1957-09-02     2*
> *

[Howard]

Darren,

When I run your solution in BBEdit, in the output the numbers that should be in Column 2 are not appearing.

Howard

[Bruce Van Allen]

Looks like the capture for \4 isn't happening, because the opening and closing parentheses are escaped, so they just stand for the characters - i.e., they don’t capture what they surround.

Try:

^\s*19(\d\d)-(\d\d)-(\d\d)\s+(\d+)\s*$

Same replacement pattern.

> Search for this grep pattern:
>
> ^\s*19(\d\d)-(\d\d)-(\d\d)\s+\(\d+\)\s*$
>
> Replace with:
>
> \2/\3/\1\t\4

_bruce__van_allen__santa_cruz_ca_
_831_429_1688_p_
_831_332_3649_c_

[Rod Buchanan]

Search: ^(\d\d\d\d-\d\d-\d\d)\s\((\d+)\)

Replace: \1\t\2

-- 
Rod

--
This is the BBEdit Talk public discussion group. If you have a feature request or need technical support, please email "sup...@barebones.com" rather than posting here. Follow @bbedit on Twitter: <https://twitter.com/bbedit>
---
You received this message because you are subscribed to the Google Groups "BBEdit Talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email to bbedit+un...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/bbedit/97329618-e692-4a7d-8351-0d661a82ad7cn%40googlegroups.com.

[Darren Duncan]

Howard,

Sorry, I had a typo, I forgot to include capturing parenthesis in my example.

I didn't actually take the time to execute what I told you to prove fitness,
since I considered it simple enough, but this was a typo and I understood how it
should have been done.

It should be this, with the extra 2 added, as Bruce and Rod pointed out:

^\s*19(\d\d)-(\d\d)-(\d\d)\s+\((\d+)\)\s*$

-- Darren Duncan

[Howard]

Thanks Bruce, Rod, and Darren for your help.

-- 

Howard