[TABLE="class: grid, width: 600"]
<tbody>[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[/TR]
[TR]
[TD]DATE
[/TD]
[TD]FREE TEXT ENTRY
[/TD]
[TD]FORMULALEFT
[/TD]
[TD]FORMULAMID
[/TD]
[TD]FORMULARIGHT
[/TD]
[TD]FORMULAMID
[/TD]
[/TR]
[TR]
[TD]09/01/18
[/TD]
[TD]234 park st northwest pl1235
[/TD]
[TD]=LEFT(B3,10)
[/TD]
[TD]=MID(B3,5,11)
[/TD]
[TD]=RIGHT(B3,10)
[/TD]
[TD]=MID(B3,6,8)
[/TD]
[/TR]
[TR]
[TD]09/01/18
[/TD]
[TD]pl1235 park st nw 234
[/TD]
[TD]=LEFT(B4,10)
[/TD]
[TD]=MID(B4,5,11)
[/TD]
[TD]=RIGHT(B4,10)
[/TD]
[TD]=MID(B4,6,8)
[/TD]
[/TR]
[TR]
[TD]09/02/18
[/TD]
[TD]park st 234 85 leafing rd
[/TD]
[TD]=LEFT(B5,10)
[/TD]
[TD]=MID(B5,5,11)
[/TD]
[TD]=RIGHT(B5,10)
[/TD]
[TD]=MID(B5,6,8)
[/TD]
[/TR]
[TR]
[TD]09/02/18
[/TD]
[TD]cycle 20 beard park st
[/TD]
[TD]=LEFT(B6,10)
[/TD]
[TD]=MID(B6,5,11)
[/TD]
[TD]=RIGHT(B6,10)
[/TD]
[TD]=MID(B6,6,8)
[/TD]
[/TR]
[TR]
[TD]09/03/18
[/TD]
[TD]123 red cup blvd pl9090
[/TD]
[TD]=LEFT(B7,10)
[/TD]
[TD]=MID(B7,5,11)
[/TD]
[TD]=RIGHT(B7,10)
[/TD]
[TD]=MID(B7,6,8)
[/TD]
[/TR]
[TR]
[TD]09/03/18
[/TD]
[TD]left 123 blue avenue pl902
[/TD]
[TD]=LEFT(B8,10)
[/TD]
[TD]=MID(B8,5,11)
[/TD]
[TD]=RIGHT(B8,10)
[/TD]
[TD]=MID(B8,6,8)
[/TD]
[/TR]
[TR]
[TD]09/03/18
[/TD]
[TD]red cup blvd 123 pl9090
[/TD]
[TD]=LEFT(B9,10)
[/TD]
[TD]=MID(B9,5,11)
[/TD]
[TD]=RIGHT(B9,10)
[/TD]
[TD]=MID(B9,6,8)
[/TD]
[/TR]
[TR]
[TD]09/04/18
[/TD]
[TD]89 e san juan pl northeast
[/TD]
[TD]=LEFT(B10,10)
[/TD]
[TD]=MID(B10,5,11)
[/TD]
[TD]=RIGHT(B10,10)
[/TD]
[TD]=MID(B10,6,8)
[/TD]
[/TR]
</tbody>[/TABLE]
Good morning,
I have a table which contains a:
COLUMN A Date column
COLUMN B text string column
COLUMN C a column with a LEFT formula that extracts 10 characters
COLUMN D a column with a MID formula that extracts 11 characters, after 5 spaces
COLUMN E a column with a RIGHT formula that extracts 10 characters
COLUMN F another column with a MID formula that extracts 8 characters, after 6 spaces
The problem and target is COLUMN B. I want to find all entries that match most or all of the words in this cell but this is a free text field and 100 employees have completed the entries in various order. Want to count all of those with the same entry as 1 but only if it has the same date.
I created COLUMNS C through COLUMN F in a effort to cut up the text string and then search for it throughout the table but I'm not doing a good job of it NOR have I been able to ONLY count if has the same date.
For example the table below shows:
2 for 09/01/2018 of those with "234" "park" "st"
1 for 09/02/2018 of those with "park" "st" "234" "85" "leafing" "rd"
1 for 09/02/2018 of those with "cycle" "20" "beard" "park" "st"
1 for 09/03/2018 of those with "123" "red" "cup" "blvd"
1 for 09/03/2018 of those with "left" "123" "blue"
1 for 09/03/2018 of those with "red" "cup" "123"
1 for 09/04/2018 of those with "89 e san juan pl"
I know I cannot use all of the words/contents located in the cell but would like to find most. I would appreciate your help immensely...I have 3 years of data like this.
<tbody>[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[/TR]
[TR]
[TD]DATE
[/TD]
[TD]FREE TEXT ENTRY
[/TD]
[TD]FORMULALEFT
[/TD]
[TD]FORMULAMID
[/TD]
[TD]FORMULARIGHT
[/TD]
[TD]FORMULAMID
[/TD]
[/TR]
[TR]
[TD]09/01/18
[/TD]
[TD]234 park st northwest pl1235
[/TD]
[TD]=LEFT(B3,10)
[/TD]
[TD]=MID(B3,5,11)
[/TD]
[TD]=RIGHT(B3,10)
[/TD]
[TD]=MID(B3,6,8)
[/TD]
[/TR]
[TR]
[TD]09/01/18
[/TD]
[TD]pl1235 park st nw 234
[/TD]
[TD]=LEFT(B4,10)
[/TD]
[TD]=MID(B4,5,11)
[/TD]
[TD]=RIGHT(B4,10)
[/TD]
[TD]=MID(B4,6,8)
[/TD]
[/TR]
[TR]
[TD]09/02/18
[/TD]
[TD]park st 234 85 leafing rd
[/TD]
[TD]=LEFT(B5,10)
[/TD]
[TD]=MID(B5,5,11)
[/TD]
[TD]=RIGHT(B5,10)
[/TD]
[TD]=MID(B5,6,8)
[/TD]
[/TR]
[TR]
[TD]09/02/18
[/TD]
[TD]cycle 20 beard park st
[/TD]
[TD]=LEFT(B6,10)
[/TD]
[TD]=MID(B6,5,11)
[/TD]
[TD]=RIGHT(B6,10)
[/TD]
[TD]=MID(B6,6,8)
[/TD]
[/TR]
[TR]
[TD]09/03/18
[/TD]
[TD]123 red cup blvd pl9090
[/TD]
[TD]=LEFT(B7,10)
[/TD]
[TD]=MID(B7,5,11)
[/TD]
[TD]=RIGHT(B7,10)
[/TD]
[TD]=MID(B7,6,8)
[/TD]
[/TR]
[TR]
[TD]09/03/18
[/TD]
[TD]left 123 blue avenue pl902
[/TD]
[TD]=LEFT(B8,10)
[/TD]
[TD]=MID(B8,5,11)
[/TD]
[TD]=RIGHT(B8,10)
[/TD]
[TD]=MID(B8,6,8)
[/TD]
[/TR]
[TR]
[TD]09/03/18
[/TD]
[TD]red cup blvd 123 pl9090
[/TD]
[TD]=LEFT(B9,10)
[/TD]
[TD]=MID(B9,5,11)
[/TD]
[TD]=RIGHT(B9,10)
[/TD]
[TD]=MID(B9,6,8)
[/TD]
[/TR]
[TR]
[TD]09/04/18
[/TD]
[TD]89 e san juan pl northeast
[/TD]
[TD]=LEFT(B10,10)
[/TD]
[TD]=MID(B10,5,11)
[/TD]
[TD]=RIGHT(B10,10)
[/TD]
[TD]=MID(B10,6,8)
[/TD]
[/TR]
</tbody>[/TABLE]
Good morning,
I have a table which contains a:
COLUMN A Date column
COLUMN B text string column
COLUMN C a column with a LEFT formula that extracts 10 characters
COLUMN D a column with a MID formula that extracts 11 characters, after 5 spaces
COLUMN E a column with a RIGHT formula that extracts 10 characters
COLUMN F another column with a MID formula that extracts 8 characters, after 6 spaces
The problem and target is COLUMN B. I want to find all entries that match most or all of the words in this cell but this is a free text field and 100 employees have completed the entries in various order. Want to count all of those with the same entry as 1 but only if it has the same date.
I created COLUMNS C through COLUMN F in a effort to cut up the text string and then search for it throughout the table but I'm not doing a good job of it NOR have I been able to ONLY count if has the same date.
For example the table below shows:
2 for 09/01/2018 of those with "234" "park" "st"
1 for 09/02/2018 of those with "park" "st" "234" "85" "leafing" "rd"
1 for 09/02/2018 of those with "cycle" "20" "beard" "park" "st"
1 for 09/03/2018 of those with "123" "red" "cup" "blvd"
1 for 09/03/2018 of those with "left" "123" "blue"
1 for 09/03/2018 of those with "red" "cup" "123"
1 for 09/04/2018 of those with "89 e san juan pl"
I know I cannot use all of the words/contents located in the cell but would like to find most. I would appreciate your help immensely...I have 3 years of data like this.