Obtaining values from Textsplit

[SeliM]

Thank you for taking time to review my enquiry.

I am assisting a local government with home care services data analysis.

With the aid of you colleagues I have been able to convert a 3rd party system export from one cell:

George E Smith-26/11/1948-1/01/2024-2-1785

To many columns:

First Name	Middle Name	Family Name	DoB	Date of Service	Hours	Cost
George	E	Smith	26/11/1948	1/01/2024	2	1785

The interesting challenge is to find a way to be able to use the Date of service, Hours and Cost in analysis.
I have been using sumproduct with if statements which work well however the organisation wishes to use PT that fit with skill levels and customary practice.

PT is ok to count but I have found it challenging to use PT for sum due to the structure of the textsplit output, (also Tables is not possible with textsplit).

Is there a solution to this challenge?

Many thanks for all your assistance over 2024 and best wishes for 2025.

Mel

 

[Cubist]

Looks like a duplicate to this thread. How is this different?

 

[Alex Blakenburg]

Although you have marked Post #2 in the previous thread as the solution, it sounds like you are using @Cubist post 7 or 8. These have been superseded by his post 10. The first 2 didn't convert the date from Text to a Date.
Peter's versions also do the conversion.

 

[SeliM]

The earlier posts were about structuring the textsplit to account for different versions of data posting such as DoB 21/1/51 compared with 21-1-51. And the various name structures such as hyphenated family names.

My query is on how to use the product of the earlier work in a pivot table when the formula result is not numeral values such as cost and hours that are the product of the textsplit formula.

I have used sumproduct with success but the organisation I'm helping wishes to use pivot tables. Is there a process that can help with that apart from reconstructing another WS with the same information by formula?

 

[Cubist]

Which solution did you use in the other thread? As Alex mentioned, some of the solutions converted to number and you should be able to perform calculations.