Formula for pulling specific information from a cell with too much and varying characters

J

Jamie0886

New Member
Joined
Jun 8, 2021
Messages
1
Office Version
  1. 2016
Platform
  1. Windows
Hi all, I have a spreadsheet where the first cell in my row contains the information I need, but also contains over 400 words/characters that I don't need and the information I need is never in the same position. The info I need is a reference number that starts with 19.

First time poster, so take it easy on me :)


I want to be able to paste this report, samples on rows 2 & 3. But I need to pull information from coloum a. I need the reference number only, found in the cell beginning withreferenceNumber (19. for example. End result should look like example below
Booking ID (6024644), Object (1565335974), 6024644 Offloaded (false), 6024644 Model (false), 6024644 OnDemandBus (false), 6024644 SMSonApproachSent (null), 6024644 FleetIdUnique (FleetIdUnique{fleetId=69, productionServerId=3}), 6024644 Status (COMPLETED), 6024644 queueEntryTime (null), 6024644 allowedZones (), 6024644 Queue (false), 6024644 EntryType (DISPATCH_CLIENT), 6024644 PhoneID (), 6024644 Is NoLoc? (false), 6024644 Is AutoDispatch? (true), 6024644 Reward Stars (null), 6024644 Reward Stars Reason (null), 6024644 Priority (NORMAL), 6024644 Pickup (Fri Jun 04 09:00:00 AEST 2021 [1622761200000] PRE - release time (600), class (class java.util.Date)), 6024644 Passenger # (1), 6024644 Addresses [# 2] (( [Address] ID (15039972) BookingID (6024644) Pass (1) DriverInfo () Name (LOVE smith) Phone (0362287051) Location ((-42.8489, 147.3006)) Source (GEOCODE) ZoneID (-1) UBD Ref () RequiredTime (Fri Jun 04 09:00:00 AEST 2021 [1622761200000]) isPickup (true) isPrebooked (true) SiteType (class com.celltrack.smartMove.geocode.GeoAddress) SitePrefix (99) Site (PERONNE AVE) Area (MOONAH) GEO (99 PERONNE AVE, MOONAH),( [Address] ID (15039973) BookingID (6024644) Pass (0) DriverInfo () Name (LOVE smith) Phone (0362287051) Location ((-42.8896, 147.3195)) Source (GEOCODE) ZoneID (-1) UBD Ref () RequiredTime (null [NULL]) isPickup (false) isPrebooked (false) SiteType (class com.celltrack.smartMove.geocode.GeoAddressPlace) SitePrefix () Site (HOBART DAY SURGERY 10 WARNEFORD ST) Area (SOUTH HOBART) GEO (HOBART DAY SURGERY 10 WARNEFORD ST, SOUTH HOBART)), 6024644 Attributes ( Attributes: (Attribute ID[15], State[Ticked];)), 6024644 BookingFares ([BookingFares] bookingId (6024644) referenceNumber (19557616) fareAmount (0.0) extrasAmount (0.0) accountAmount (0.0) requiresAccountRef (true) fixedPriceOnly (false) maxSubsidyPercent (0) maxSubsidyCents (0) requiresOdometer (false) m_bookingPayments obj ([[BookingPayments] bookingId (6024644) PaymentNumber (1) PaymentType (2) == BookingPaymentsType (Account/Voucher) AccountNumber (23) ReferenceNumber (19557616) PaymentAmount (0.0) PaymentPercent (1000) PaymentMaxAmount (0.0) RequiresRef (1) LiftFeeFlag (0)]) [BookingPayments] bookingId (6024644) PaymentNumber (1) PaymentType (2) == BookingPaymentsType (Account/Voucher) AccountNumber (23) ReferenceNumber (19557616) PaymentAmount (0.0) PaymentPercent (1000) PaymentMaxAmount (0.0) RequiresRef (1) LiftFeeFlag (0)), 6024644 Plotted? Driver (0) Vehicle (0) Time (null), 6024644 Client (Client Id (12877), Phone (NULL), Names (), Emails (), AccountIds (), Notes (NULL), DriverInfo (NULL) HashCodeWithAddresses (1146730048) HashCode (1146730048), Addresses (), Include Drivers (), Exclude Drivers (), Include Vehicles (), Exclude Vehicles (), Attributes ()), 6024644 m_vehicleId (0), jobDetails Recvd (null), 6024644 Offer (none), 6024644 Job (none), 6024644 Updates (1) ([BookingUpdate] Date (2021-06-03 16:59:18.0), operatorId (3001430), descr (Booking Created), descr details ([Existing Client]), debug (false));, 6024644 History (5), 6024644 ([BookingHistory] ID (28612958) status (QUEUED) offerId (0) when (2021-06-04 08:50:01.0) driverId (0) vehicleId (0) offerZone (0) offerLoc ((0.0000, 0.0000)) endReason (PreBooking Queued at 04/06/21 08:50:01) jobId (0) jobSentDate (null) jobBookedDate (null) jobPickupDate (null) jobCompleteDate (null));, 6024644 ([BookingHistory] ID (28612960) status (OFFERED) offerId (9977373) when (2021-06-04 08:50:01.0) driverId (3001313) vehicleId (77) offerZone (97) offerLoc ((-42.8477, 147.2959)) endReason (Same Zone - Waiting Longest) jobId (0) jobSentDate (null) jobBookedDate (null) jobPickupDate (null) jobCompleteDate (null));, 6024644 ([BookingHistory] ID (28612965) status (BOOKED) offerId (9977373) when (2021-06-04 08:50:01.0) driverId (3001313) vehicleId (77) offerZone (97) offerLoc ((-42.8477, 147.2959)) endReason () jobId (7231092) jobSentDate (null) jobBookedDate (2021-06-04 08:50:04.0) jobPickupDate (null) jobCompleteDate (null));, 6024644 ([BookingHistory] ID (28613097) status (ENGAGED) offerId (9977373) when (2021-06-04 08:50:01.0) driverId (3001313) vehicleId (77) offerZone (97) offerLoc ((-42.8477, 147.2959)) endReason (Started with DIGITAL IO) jobId (7231092) jobSentDate (2021-06-04 08:50:05.0) jobBookedDate (2021-06-04 08:50:04.0) jobPickupDate (2021-06-04 08:57:26.0) jobCompleteDate (null));, 6024644 ([BookingHistory] ID (28613268) status (COMPLETED) offerId (9977373) when (2021-06-04 08:50:01.0) driverId (3001313) vehicleId (77) offerZone (97) offerLoc ((-42.8477, 147.2959)) endReason (Completed with DIGITAL IO) jobId (7231092) jobSentDate (2021-06-04 08:50:05.0) jobBookedDate (2021-06-04 08:50:04.0) jobPickupDate (2021-06-04 08:57:26.0) jobCompleteDate (2021-06-04 09:11:31.0));
FALSE​
6024644​
Fri Jun 04 09:00:00 AEST 202199 PERONNE AVE, MOONAHLOVE smithHOBART DAY SURGERY 10 WARNEFORD ST, SOUTH HOBART
50:01.0​
57:26.0​
11:31.0​
77​
3001313​
COMPLETEDEmily Gregory
Booking ID (6024636), Object (1979042151), 6024636 Offloaded (false), 6024636 Model (false), 6024636 OnDemandBus (false), 6024636 SMSonApproachSent (null), 6024636 FleetIdUnique (FleetIdUnique{fleetId=69, productionServerId=3}), 6024636 Status (COMPLETED), 6024636 queueEntryTime (null), 6024636 allowedZones (), 6024636 Queue (false), 6024636 EntryType (DISPATCH_CLIENT), 6024636 PhoneID (), 6024636 Is NoLoc? (false), 6024636 Is AutoDispatch? (true), 6024636 Reward Stars (null), 6024636 Reward Stars Reason (null), 6024636 Priority (HIGH), 6024636 Pickup (Fri Jun 04 10:45:00 AEST 2021 [1622767500000] PRE - release time (900), class (class java.util.Date)), 6024636 Passenger # (1), 6024636 Addresses [# 2] (( [Address] ID (15042907) BookingID (6024636) Pass (1) DriverInfo (please drive down steep driveway to the unit , please call on arrival 0400754657/ ) Name (Phil smith) Phone (62479334) Location ((-42.9025, 147.4589)) Source (GEOCODE) ZoneID (-1) UBD Ref () RequiredTime (Fri Jun 04 10:45:00 AEST 2021 [1622767500000]) isPickup (true) isPrebooked (true) SiteType (class com.celltrack.smartMove.geocode.GeoAddress) SitePrefix (99) Site (OAKDOWNS PARADE) Area (OAKDOWNS) GEO (99 OAKDOWNS PARADE, OAKDOWNS),( [Address] ID (15042908) BookingID (6024636) Pass (0) DriverInfo () Name (Phil smith) Phone (62479334) Location ((-42.8808, 147.3293)) Source (ZONEDEFAULT) ZoneID (-1) UBD Ref () RequiredTime (null [NULL]) isPickup (false) isPrebooked (false) SiteType (class com.celltrack.smartMove.geocode.GeoAddressPlace) SitePrefix () Site (HOBART CARDIOLOGY 25 ARGYLE ST) Area (HOBART) GEO (HOBART CARDIOLOGY 25 ARGYLE ST, HOBART)), 6024636 Attributes ( Attributes: (Attribute ID[15], State[Ticked];Attribute ID[16], State[Ticked];Attribute ID[18], State[Ticked];)), 6024636 BookingFares ([BookingFares] bookingId (6024636) referenceNumber (19555284) fareAmount (0.0) extrasAmount (0.0) accountAmount (0.0) requiresAccountRef (true) fixedPriceOnly (false) maxSubsidyPercent (0) maxSubsidyCents (0) requiresOdometer (false) m_bookingPayments obj ([[BookingPayments] bookingId (6024636) PaymentNumber (1) PaymentType (2) == BookingPaymentsType (Account/Voucher) AccountNumber (23) ReferenceNumber (19555284) PaymentAmount (0.0) PaymentPercent (1000) PaymentMaxAmount (0.0) RequiresRef (1) LiftFeeFlag (0)]) [BookingPayments] bookingId (6024636) PaymentNumber (1) PaymentType (2) == BookingPaymentsType (Account/Voucher) AccountNumber (23) ReferenceNumber (19555284) PaymentAmount (0.0) PaymentPercent (1000) PaymentMaxAmount (0.0) RequiresRef (1) LiftFeeFlag (0)), 6024636 Plotted? Driver (0) Vehicle (0) Time (null), 6024636 Client (Client Id (279492), Phone (NULL), Names (), Emails (), AccountIds (), Notes (NULL), DriverInfo (NULL) HashCodeWithAddresses (1628466607) HashCode (1628466607), Addresses (), Include Drivers (), Exclude Drivers (), Include Vehicles (), Exclude Vehicles (), Attributes ()), 6024636 m_vehicleId (0), jobDetails Recvd (null), 6024636 Offer (none), 6024636 Job (none), 6024636 Updates (2) ([BookingUpdate] Date (2021-06-03 16:55:18.0), operatorId (3001430), descr (Booking Created), descr details ([Existing Client]), debug (false)); ([BookingUpdate] Date (2021-06-04 10:39:07.0), operatorId (-101), descr (Booking Updated), descr details ([Priority changed from NORMAL to HIGH.]), debug (false));, 6024636 History (9), 6024636 ([BookingHistory] ID (28614270) status (QUEUED) offerId (0) when (2021-06-04 10:30:01.0) driverId (0) vehicleId (0) offerZone (0) offerLoc ((0.0000, 0.0000)) endReason (PreBooking Queued at 04/06/21 10:30:01) jobId (0) jobSentDate (null) jobBookedDate (null) jobPickupDate (null) jobCompleteDate (null));, 6024636 ([BookingHistory] ID (28614273) status (OFFERED) offerId (9977753) when (2021-06-04 10:30:01.0) driverId (3000698) vehicleId (31) offerZone (36) offerLoc ((-42.8563, 147.3874)) endReason (Searching Surrounding Zones) jobId (0) jobSentDate (null) jobBookedDate (null) jobPickupDate (null) jobCompleteDate (null));, 6024636 ([BookingHistory] ID (28614280) status (BOOKED) offerId (9977753) when (2021-06-04 10:30:01.0) driverId (3000698) vehicleId (31) offerZone (36) offerLoc ((-42.8563, 147.3874)) endReason () jobId (7231421) jobSentDate (null) jobBookedDate (2021-06-04 10:30:04.0) jobPickupDate (null) jobCompleteDate (null));, 6024636 ([BookingHistory] ID (28614462) status (RESUBMITTED) offerId (9977753) when (2021-06-04 10:30:01.0) driverId (3000698) vehicleId (31) offerZone (36) offerLoc ((-42.8563, 147.3874)) endReason (OTHER - Penalty 20 m) jobId (7231421) jobSentDate (2021-06-04 10:30:04.0) jobBookedDate (2021-06-04 10:30:04.0) jobPickupDate (null) jobCompleteDate (2021-06-04 10:39:03.0));, 6024636 ([BookingHistory] ID (28614463) status (QUEUED) offerId (9977753) when (2021-06-04 10:39:05.0) driverId (3000698) vehicleId (31) offerZone (0) offerLoc ((0.0000, 0.0000)) endReason (Vehicle 075 has Resubmitted 1 times - No longer eligible) jobId (0) jobSentDate (null) jobBookedDate (null) jobPickupDate (null) jobCompleteDate (null));, 6024636 ([BookingHistory] ID (28614466) status (OFFERED) offerId (9977810) when (2021-06-04 10:39:11.0) driverId (3003433) vehicleId (116) offerZone (36) offerLoc ((-42.8661, 147.4238)) endReason (URGENT: Searching Surrounding Zones) jobId (0) jobSentDate (null) jobBookedDate (null) jobPickupDate (null) jobCompleteDate (null));, 6024636 ([BookingHistory] ID (28614469) status (BOOKED) offerId (9977810) when (2021-06-04 10:39:11.0) driverId (3003433) vehicleId (116) offerZone (36) offerLoc ((-42.8661, 147.4238)) endReason () jobId (7231455) jobSentDate (null) jobBookedDate (2021-06-04 10:39:13.0) jobPickupDate (null) jobCompleteDate (null));, 6024636 ([BookingHistory] ID (28614652) status (ENGAGED) offerId (9977810) when (2021-06-04 10:39:11.0) driverId (3003433) vehicleId (116) offerZone (36) offerLoc ((-42.8661, 147.4238)) endReason (Started with DIGITAL IO) jobId (7231455) jobSentDate (2021-06-04 10:39:14.0) jobBookedDate (2021-06-04 10:39:13.0) jobPickupDate (2021-06-04 10:47:50.0) jobCompleteDate (null));, 6024636 ([BookingHistory] ID (28615169) status (COMPLETED) offerId (9977810) when (2021-06-04 10:39:11.0) driverId (3003433) vehicleId (116) offerZone (36) offerLoc ((-42.8661, 147.4238)) endReason (Completed with DIGITAL IO) jobId (7231455) jobSentDate (2021-06-04 10:39:14.0) jobBookedDate (2021-06-04 10:39:13.0) jobPickupDate (2021-06-04 10:47:50.0) jobCompleteDate (2021-06-04 11:10:22.0));
FALSE​
6024636​
Fri Jun 04 10:45:00 AEST 202199 OAKDOWNS PARADE, OAKDOWNSPHil smithHOBART CARDIOLOGY 25 ARGYLE ST, HOBART
39:11.0​
47:50.0​
10:22.0​
116​
3003433​
COMPLETEDEmily Gregory
Example
Booking ID (6024644), Object (1565335974), 6024644 Offloaded (false), 6024644 Model (false), 6024644 OnDemandBus (false), 6024644 SMSonApproachSent (null), 6024644 FleetIdUnique (FleetIdUnique{fleetId=69, productionServerId=3}), 6024644 Status (COMPLETED), 6024644 queueEntryTime (null), 6024644 allowedZones (), 6024644 Queue (false), 6024644 EntryType (DISPATCH_CLIENT), 6024644 PhoneID (), 6024644 Is NoLoc? (false), 6024644 Is AutoDispatch? (true), 6024644 Reward Stars (null), 6024644 Reward Stars Reason (null), 6024644 Priority (NORMAL), 6024644 Pickup (Fri Jun 04 09:00:00 AEST 2021 [1622761200000] PRE - release time (600), class (class java.util.Date)), 6024644 Passenger # (1), 6024644 Addresses [# 2] (( [Address] ID (15039972) BookingID (6024644) Pass (1) DriverInfo () Name (LOVE smith) Phone (0362287051) Location ((-42.8489, 147.3006)) Source (GEOCODE) ZoneID (-1) UBD Ref () RequiredTime (Fri Jun 04 09:00:00 AEST 2021 [1622761200000]) isPickup (true) isPrebooked (true) SiteType (class com.celltrack.smartMove.geocode.GeoAddress) SitePrefix (99) Site (PERONNE AVE) Area (MOONAH) GEO (99 PERONNE AVE, MOONAH),( [Address] ID (15039973) BookingID (6024644) Pass (0) DriverInfo () Name (LOVE smith) Phone (0362287051) Location ((-42.8896, 147.3195)) Source (GEOCODE) ZoneID (-1) UBD Ref () RequiredTime (null [NULL]) isPickup (false) isPrebooked (false) SiteType (class com.celltrack.smartMove.geocode.GeoAddressPlace) SitePrefix () Site (HOBART DAY SURGERY 10 WARNEFORD ST) Area (SOUTH HOBART) GEO (HOBART DAY SURGERY 10 WARNEFORD ST, SOUTH HOBART)), 6024644 Attributes ( Attributes: (Attribute ID[15], State[Ticked];)), 6024644 BookingFares ([BookingFares] bookingId (6024644) referenceNumber (19557616) fareAmount (0.0) extrasAmount (0.0) accountAmount (0.0) requiresAccountRef (true) fixedPriceOnly (false) maxSubsidyPercent (0) maxSubsidyCents (0) requiresOdometer (false) m_bookingPayments obj ([[BookingPayments] bookingId (6024644) PaymentNumber (1) PaymentType (2) == BookingPaymentsType (Account/Voucher) AccountNumber (23) ReferenceNumber (19557616) PaymentAmount (0.0) PaymentPercent (1000) PaymentMaxAmount (0.0) RequiresRef (1) LiftFeeFlag (0)]) [BookingPayments] bookingId (6024644) PaymentNumber (1) PaymentType (2) == BookingPaymentsType (Account/Voucher) AccountNumber (23) ReferenceNumber (19557616) PaymentAmount (0.0) PaymentPercent (1000) PaymentMaxAmount (0.0) RequiresRef (1) LiftFeeFlag (0)), 6024644 Plotted? Driver (0) Vehicle (0) Time (null), 6024644 Client (Client Id (12877), Phone (NULL), Names (), Emails (), AccountIds (), Notes (NULL), DriverInfo (NULL) HashCodeWithAddresses (1146730048) HashCode (1146730048), Addresses (), Include Drivers (), Exclude Drivers (), Include Vehicles (), Exclude Vehicles (), Attributes ()), 6024644 m_vehicleId (0), jobDetails Recvd (null), 6024644 Offer (none), 6024644 Job (none), 6024644 Updates (1) ([BookingUpdate] Date (2021-06-03 16:59:18.0), operatorId (3001430), descr (Booking Created), descr details ([Existing Client]), debug (false));, 6024644 History (5), 6024644 ([BookingHistory] ID (28612958) status (QUEUED) offerId (0) when (2021-06-04 08:50:01.0) driverId (0) vehicleId (0) offerZone (0) offerLoc ((0.0000, 0.0000)) endReason (PreBooking Queued at 04/06/21 08:50:01) jobId (0) jobSentDate (null) jobBookedDate (null) jobPickupDate (null) jobCompleteDate (null));, 6024644 ([BookingHistory] ID (28612960) status (OFFERED) offerId (9977373) when (2021-06-04 08:50:01.0) driverId (3001313) vehicleId (77) offerZone (97) offerLoc ((-42.8477, 147.2959)) endReason (Same Zone - Waiting Longest) jobId (0) jobSentDate (null) jobBookedDate (null) jobPickupDate (null) jobCompleteDate (null));, 6024644 ([BookingHistory] ID (28612965) status (BOOKED) offerId (9977373) when (2021-06-04 08:50:01.0) driverId (3001313) vehicleId (77) offerZone (97) offerLoc ((-42.8477, 147.2959)) endReason () jobId (7231092) jobSentDate (null) jobBookedDate (2021-06-04 08:50:04.0) jobPickupDate (null) jobCompleteDate (null));, 6024644 ([BookingHistory] ID (28613097) status (ENGAGED) offerId (9977373) when (2021-06-04 08:50:01.0) driverId (3001313) vehicleId (77) offerZone (97) offerLoc ((-42.8477, 147.2959)) endReason (Started with DIGITAL IO) jobId (7231092) jobSentDate (2021-06-04 08:50:05.0) jobBookedDate (2021-06-04 08:50:04.0) jobPickupDate (2021-06-04 08:57:26.0) jobCompleteDate (null));, 6024644 ([BookingHistory] ID (28613268) status (COMPLETED) offerId (9977373) when (2021-06-04 08:50:01.0) driverId (3001313) vehicleId (77) offerZone (97) offerLoc ((-42.8477, 147.2959)) endReason (Completed with DIGITAL IO) jobId (7231092) jobSentDate (2021-06-04 08:50:05.0) jobBookedDate (2021-06-04 08:50:04.0) jobPickupDate (2021-06-04 08:57:26.0) jobCompleteDate (2021-06-04 09:11:31.0));
19557616​
6024644​
Fri Jun 04 09:00:00 AEST 202199 PERONNE AVE, MOONAHLOVE smithHOBART DAY SURGERY 10 WARNEFORD ST, SOUTH HOBART
50:01.0​
57:26.0​
11:31.0​
77​
3001313​
COMPLETEDEmily Gregory
Booking ID (6024636), Object (1979042151), 6024636 Offloaded (false), 6024636 Model (false), 6024636 OnDemandBus (false), 6024636 SMSonApproachSent (null), 6024636 FleetIdUnique (FleetIdUnique{fleetId=69, productionServerId=3}), 6024636 Status (COMPLETED), 6024636 queueEntryTime (null), 6024636 allowedZones (), 6024636 Queue (false), 6024636 EntryType (DISPATCH_CLIENT), 6024636 PhoneID (), 6024636 Is NoLoc? (false), 6024636 Is AutoDispatch? (true), 6024636 Reward Stars (null), 6024636 Reward Stars Reason (null), 6024636 Priority (HIGH), 6024636 Pickup (Fri Jun 04 10:45:00 AEST 2021 [1622767500000] PRE - release time (900), class (class java.util.Date)), 6024636 Passenger # (1), 6024636 Addresses [# 2] (( [Address] ID (15042907) BookingID (6024636) Pass (1) DriverInfo (please drive down steep driveway to the unit , please call on arrival 0400754657/ ) Name (Phil smith) Phone (62479334) Location ((-42.9025, 147.4589)) Source (GEOCODE) ZoneID (-1) UBD Ref () RequiredTime (Fri Jun 04 10:45:00 AEST 2021 [1622767500000]) isPickup (true) isPrebooked (true) SiteType (class com.celltrack.smartMove.geocode.GeoAddress) SitePrefix (99) Site (OAKDOWNS PARADE) Area (OAKDOWNS) GEO (99 OAKDOWNS PARADE, OAKDOWNS),( [Address] ID (15042908) BookingID (6024636) Pass (0) DriverInfo () Name (Phil smith) Phone (62479334) Location ((-42.8808, 147.3293)) Source (ZONEDEFAULT) ZoneID (-1) UBD Ref () RequiredTime (null [NULL]) isPickup (false) isPrebooked (false) SiteType (class com.celltrack.smartMove.geocode.GeoAddressPlace) SitePrefix () Site (HOBART CARDIOLOGY 25 ARGYLE ST) Area (HOBART) GEO (HOBART CARDIOLOGY 25 ARGYLE ST, HOBART)), 6024636 Attributes ( Attributes: (Attribute ID[15], State[Ticked];Attribute ID[16], State[Ticked];Attribute ID[18], State[Ticked];)), 6024636 BookingFares ([BookingFares] bookingId (6024636) referenceNumber (19555284) fareAmount (0.0) extrasAmount (0.0) accountAmount (0.0) requiresAccountRef (true) fixedPriceOnly (false) maxSubsidyPercent (0) maxSubsidyCents (0) requiresOdometer (false) m_bookingPayments obj ([[BookingPayments] bookingId (6024636) PaymentNumber (1) PaymentType (2) == BookingPaymentsType (Account/Voucher) AccountNumber (23) ReferenceNumber (19555284) PaymentAmount (0.0) PaymentPercent (1000) PaymentMaxAmount (0.0) RequiresRef (1) LiftFeeFlag (0)]) [BookingPayments] bookingId (6024636) PaymentNumber (1) PaymentType (2) == BookingPaymentsType (Account/Voucher) AccountNumber (23) ReferenceNumber (19555284) PaymentAmount (0.0) PaymentPercent (1000) PaymentMaxAmount (0.0) RequiresRef (1) LiftFeeFlag (0)), 6024636 Plotted? Driver (0) Vehicle (0) Time (null), 6024636 Client (Client Id (279492), Phone (NULL), Names (), Emails (), AccountIds (), Notes (NULL), DriverInfo (NULL) HashCodeWithAddresses (1628466607) HashCode (1628466607), Addresses (), Include Drivers (), Exclude Drivers (), Include Vehicles (), Exclude Vehicles (), Attributes ()), 6024636 m_vehicleId (0), jobDetails Recvd (null), 6024636 Offer (none), 6024636 Job (none), 6024636 Updates (2) ([BookingUpdate] Date (2021-06-03 16:55:18.0), operatorId (3001430), descr (Booking Created), descr details ([Existing Client]), debug (false)); ([BookingUpdate] Date (2021-06-04 10:39:07.0), operatorId (-101), descr (Booking Updated), descr details ([Priority changed from NORMAL to HIGH.]), debug (false));, 6024636 History (9), 6024636 ([BookingHistory] ID (28614270) status (QUEUED) offerId (0) when (2021-06-04 10:30:01.0) driverId (0) vehicleId (0) offerZone (0) offerLoc ((0.0000, 0.0000)) endReason (PreBooking Queued at 04/06/21 10:30:01) jobId (0) jobSentDate (null) jobBookedDate (null) jobPickupDate (null) jobCompleteDate (null));, 6024636 ([BookingHistory] ID (28614273) status (OFFERED) offerId (9977753) when (2021-06-04 10:30:01.0) driverId (3000698) vehicleId (31) offerZone (36) offerLoc ((-42.8563, 147.3874)) endReason (Searching Surrounding Zones) jobId (0) jobSentDate (null) jobBookedDate (null) jobPickupDate (null) jobCompleteDate (null));, 6024636 ([BookingHistory] ID (28614280) status (BOOKED) offerId (9977753) when (2021-06-04 10:30:01.0) driverId (3000698) vehicleId (31) offerZone (36) offerLoc ((-42.8563, 147.3874)) endReason () jobId (7231421) jobSentDate (null) jobBookedDate (2021-06-04 10:30:04.0) jobPickupDate (null) jobCompleteDate (null));, 6024636 ([BookingHistory] ID (28614462) status (RESUBMITTED) offerId (9977753) when (2021-06-04 10:30:01.0) driverId (3000698) vehicleId (31) offerZone (36) offerLoc ((-42.8563, 147.3874)) endReason (OTHER - Penalty 20 m) jobId (7231421) jobSentDate (2021-06-04 10:30:04.0) jobBookedDate (2021-06-04 10:30:04.0) jobPickupDate (null) jobCompleteDate (2021-06-04 10:39:03.0));, 6024636 ([BookingHistory] ID (28614463) status (QUEUED) offerId (9977753) when (2021-06-04 10:39:05.0) driverId (3000698) vehicleId (31) offerZone (0) offerLoc ((0.0000, 0.0000)) endReason (Vehicle 075 has Resubmitted 1 times - No longer eligible) jobId (0) jobSentDate (null) jobBookedDate (null) jobPickupDate (null) jobCompleteDate (null));, 6024636 ([BookingHistory] ID (28614466) status (OFFERED) offerId (9977810) when (2021-06-04 10:39:11.0) driverId (3003433) vehicleId (116) offerZone (36) offerLoc ((-42.8661, 147.4238)) endReason (URGENT: Searching Surrounding Zones) jobId (0) jobSentDate (null) jobBookedDate (null) jobPickupDate (null) jobCompleteDate (null));, 6024636 ([BookingHistory] ID (28614469) status (BOOKED) offerId (9977810) when (2021-06-04 10:39:11.0) driverId (3003433) vehicleId (116) offerZone (36) offerLoc ((-42.8661, 147.4238)) endReason () jobId (7231455) jobSentDate (null) jobBookedDate (2021-06-04 10:39:13.0) jobPickupDate (null) jobCompleteDate (null));, 6024636 ([BookingHistory] ID (28614652) status (ENGAGED) offerId (9977810) when (2021-06-04 10:39:11.0) driverId (3003433) vehicleId (116) offerZone (36) offerLoc ((-42.8661, 147.4238)) endReason (Started with DIGITAL IO) jobId (7231455) jobSentDate (2021-06-04 10:39:14.0) jobBookedDate (2021-06-04 10:39:13.0) jobPickupDate (2021-06-04 10:47:50.0) jobCompleteDate (null));, 6024636 ([BookingHistory] ID (28615169) status (COMPLETED) offerId (9977810) when (2021-06-04 10:39:11.0) driverId (3003433) vehicleId (116) offerZone (36) offerLoc ((-42.8661, 147.4238)) endReason (Completed with DIGITAL IO) jobId (7231455) jobSentDate (2021-06-04 10:39:14.0) jobBookedDate (2021-06-04 10:39:13.0) jobPickupDate (2021-06-04 10:47:50.0) jobCompleteDate (2021-06-04 11:10:22.0));
19555284​
6024636​
Fri Jun 04 10:45:00 AEST 202199 OAKDOWNS PARADE, OAKDOWNSPHil smithHOBART CARDIOLOGY 25 ARGYLE ST, HOBART
39:11.0​
47:50.0​
10:22.0​
116​
3003433​
COMPLETEDEmily Gregory
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Sort by date Sort by votes
Try my UDF. In worksheet, B1 = ReferNumbers (A1)
VBA Code: 
Function ReferNumbers(rng As Range)
    Dim mcol As Object
    Dim a, m
    Dim ii As Integer

    ReDim a(1 To 20)
    With CreateObject("VBScript.RegExp")
        .Pattern = "19\d+"
        .Global = True
        Set mcol = .Execute(rng.Value)
        For Each m In mcol
            ii = ii + 1
            a(ii) = m
        Next
    End With
    ReDim Preserve a(1 To ii)
    ReferNumbers = Join(a, "; ")
End Function
 
Upvote 0
Try

Book1
ABCDEFGHIJKL
1DescriptionIDPickupAddressDriver NameAddressWhenJob Pickup TimeJob Complete TimeVehicle IDDriver ID
2 PRE - release time (600), class (class java.util.Date)), 6024644 Passenger # (1), 6024644 Addresses [# 2] (( [Address] ID (15039972) BookingID (6024644) Pass (1) DriverInfo () Name (LOVE smith) Phone (0362287051) Location ((-42.8489, 147.3006)) Source (GEOCODE) ZoneID (-1) UBD Ref () RequiredTime (Fri Jun 04 09:00:00 AEST 2021 [1622761200000]) isPickup (true) isPrebooked (true) SiteType (class com.celltrack.smartMove.geocode.GeoAddress) SitePrefix (99) Site (PERONNE AVE) Area (MOONAH) GEO (99 PERONNE AVE, MOONAH),( [Address] ID (15039973) BookingID (6024644) Pass (0) DriverInfo () Name (LOVE smith) Phone (0362287051) Location ((-42.8896, 147.3195)) Source (GEOCODE) ZoneID (-1) UBD Ref () RequiredTime (null [NULL]) isPickup (false) isPrebooked (false) SiteType (class com.celltrack.smartMove.geocode.GeoAddressPlace) SitePrefix () Site (HOBART DAY SURGERY 10 WARNEFORD ST) Area (SOUTH HOBART) GEO (HOBART DAY SURGERY 10 WARNEFORD ST, SOUTH HOBART)), 6024644 Attributes ( Attributes: (Attribute ID[15], State[Ticked]), 6024644 BookingFares ([BookingFares] bookingId (6024644) referenceNumber (19557616) fareAmount (0.0) extrasAmount (0.0) accountAmount (0.0) requiresAccountRef (true) fixedPriceOnly (false) maxSubsidyPercent (0) maxSubsidyCents (0) requiresOdometer (false) m_bookingPayments obj ([[BookingPayments] bookingId (6024644) PaymentNumber (1) PaymentType (2) == BookingPaymentsType (Account/Voucher) AccountNumber (23) ReferenceNumber (19557616) PaymentAmount (0.0) PaymentPercent (1000) PaymentMaxAmount (0.0) RequiresRef (1) LiftFeeFlag (0)]) [BookingPayments] bookingId (6024644) PaymentNumber (1) PaymentType (2) == BookingPaymentsType (Account/Voucher) AccountNumber (23) ReferenceNumber (19557616) PaymentAmount (0.0) PaymentPercent (1000) PaymentMaxAmount (0.0) RequiresRef (1) LiftFeeFlag (0)), 6024644 Plotted? Driver (0) Vehicle (0) Time (null), 6024644 Client (Client Id (12877), Phone (NULL), Names (), Emails (), AccountIds (), Notes (NULL), DriverInfo (NULL) HashCodeWithAddresses (1146730048) HashCode (1146730048), Addresses (), Include Drivers (), Exclude Drivers (), Include Vehicles (), Exclude Vehicles (), Attributes ()), 6024644 m_vehicleId (0), jobDetails Recvd (null), 6024644 Offer (none), 6024644 Job (none), 6024644 Updates (1) ([BookingUpdate] Date (2021-06-03 16:59:18.0), operatorId (3001430), descr (Booking Created), descr details ([Existing Client]), debug (false));, 6024644 History (5), 6024644 ([BookingHistory] ID (28612958) status (QUEUED) offerId (0) when (2021-06-04 08:50:01.0) driverId (0) vehicleId (0) offerZone (0) offerLoc ((0.0000, 0.0000)) endReason (PreBooking Queued at 04/06/21 08:50:01) jobId (0) jobSentDate (null) jobBookedDate (null) jobPickupDate (null) jobCompleteDate (null));, 6024644 ([BookingHistory] ID (28612960) status (OFFERED) offerId (9977373) when (2021-06-04 08:50:01.0) driverId (3001313) vehicleId (77) offerZone (97) offerLoc ((-42.8477, 147.2959)) endReason (Same Zone - Waiting Longest) jobId (0) jobSentDate (null) jobBookedDate (null) jobPickupDate (null) jobCompleteDate (null));, 6024644 ([BookingHistory] ID (28612965) status (BOOKED) offerId (9977373) when (2021-06-04 08:50:01.0) driverId (3001313) vehicleId (77) offerZone (97) offerLoc ((-42.8477, 147.2959)) endReason () jobId (7231092) jobSentDate (null) jobBookedDate (2021-06-04 08:50:04.0) jobPickupDate (null) jobCompleteDate (null));, 6024644 ([BookingHistory] ID (28613097) status (ENGAGED) offerId (9977373) when (2021-06-04 08:50:01.0) driverId (3001313) vehicleId (77) offerZone (97) offerLoc ((-42.8477, 147.2959)) endReason (Started with DIGITAL IO) jobId (7231092) jobSentDate (2021-06-04 08:50:05.0) jobBookedDate (2021-06-04 08:50:04.0) jobPickupDate (2021-06-04 08:57:26.0) jobCompleteDate (null));, 6024644 ([BookingHistory] ID (28613268) status (COMPLETED) offerId (9977373) when (2021-06-04 08:50:01.0) driverId (3001313) vehicleId (77) offerZone (97) offerLoc ((-42.8477, 147.2959)) endReason (Completed with DIGITAL IO) jobId (7231092) jobSentDate (2021-06-04 08:50:05.0) jobBookedDate (2021-06-04 08:50:04.0) jobPickupDate (2021-06-04 08:57:26.0) jobCompleteDate (2021-06-04 09:11:31.0));]Booking ID (6024644), Object (1565335974), 6024644 Offloaded (false), 6024644 Model (false), 6024644 OnDemandBus (false), 6024644 SMSonApproachSent (null), 6024644 FleetIdUnique (FleetIdUnique{fleetId=69, productionServerId=3}), 6024644 Status (COMPLETED), 6024644 queueEntryTime (null), 6024644 allowedZones (), 6024644 Queue (false), 6024644 EntryType (DISPATCH_CLIENT), 6024644 PhoneID (), 6024644 Is NoLoc? (false), 6024644 Is AutoDispatch? (true), 6024644 Reward Stars (null), 6024644 Reward Stars Reason (null), 6024644 Priority (NORMAL), 6024644 Pickup (Fri Jun 04 09:00:00 AEST 2021 [1622761200000] PRE - release time (600), class (class java.util.Date)), 6024644 Passenger # (1), 6024644 Addresses [# 2] (( [Address] ID (15039972) BookingID (6024644) Pass (1) DriverInfo () Name (LOVE smith) Phone (0362287051) Location ((-42.8489, 147.3006)) Source (GEOCODE) ZoneID (-1) UBD Ref () RequiredTime (Fri Jun 04 09:00:00 AEST 2021 [1622761200000]) isPickup (true) isPrebooked (true) SiteType (class com.celltrack.smartMove.geocode.GeoAddress) SitePrefix (99) Site (PERONNE AVE) Area (MOONAH) GEO (99 PERONNE AVE, MOONAH),( [Address] ID (15039973) BookingID (6024644) Pass (0) DriverInfo () Name (LOVE smith) Phone (0362287051) Location ((-42.8896, 147.3195)) Source (GEOCODE) ZoneID (-1) UBD Ref () RequiredTime (null [NULL]) isPickup (false) isPrebooked (false) SiteType (class com.celltrack.smartMove.geocode.GeoAddressPlace) SitePrefix () Site (HOBART DAY SURGERY 10 WARNEFORD ST) Area (SOUTH HOBART) GEO (HOBART DAY SURGERY 10 WARNEFORD ST, SOUTH HOBART)), 6024644 Attributes ( Attributes: (Attribute ID[15], State[Ticked]), 6024644 BookingFares ([BookingFares] bookingId (6024644) referenceNumber (19557616) fareAmount (0.0) extrasAmount (0.0) accountAmount (0.0) requiresAccountRef (true) fixedPriceOnly (false) maxSubsidyPercent (0) maxSubsidyCents (0) requiresOdometer (false) m_bookingPayments obj ([[BookingPayments] bookingId (6024644) PaymentNumber (1) PaymentType (2) == BookingPaymentsType (Account/Voucher) AccountNumber (23) ReferenceNumber (19557616) PaymentAmount (0.0) PaymentPercent (1000) PaymentMaxAmount (0.0) RequiresRef (1) LiftFeeFlag (0)]) [BookingPayments] bookingId (6024644) PaymentNumber (1) PaymentType (2) == BookingPaymentsType (Account/Voucher) AccountNumber (23) ReferenceNumber (19557616) PaymentAmount (0.0) PaymentPercent (1000) PaymentMaxAmount (0.0) RequiresRef (1) LiftFeeFlag (0)), 6024644 Plotted? Driver (0) Vehicle (0) Time (null), 6024644 Client (Client Id (12877), Phone (NULL), Names (), Emails (), AccountIds (), Notes (NULL), DriverInfo (NULL) HashCodeWithAddresses (1146730048) HashCode (1146730048), Addresses (), Include Drivers (), Exclude Drivers (), Include Vehicles (), Exclude Vehicles (), Attributes ()), 6024644 m_vehicleId (0), jobDetails Recvd (null), 6024644 Offer (none), 6024644 Job (none), 6024644 Updates (1) ([BookingUpdate] Date (2021-06-03 16:59:18.0), operatorId (3001430), descr (Booking Created), descr details ([Existing Client]), debug (false));, 6024644 History (5), 6024644 ([BookingHistory] ID (28612958) status (QUEUED) offerId (0) when (2021-06-04 08:50:01.0) driverId (0) vehicleId (0) offerZone (0) offerLoc ((0.0000, 0.0000)) endReason (PreBooking Queued at 04/06/21 08:50:01) jobId (0) jobSentDate (null) jobBookedDate (null) jobPickupDate (null) jobCompleteDate (null));, 6024644 ([BookingHistory] ID (28612960) status (OFFERED) offerId (9977373) when (2021-06-04 08:50:01.0) driverId (3001313) vehicleId (77) offerZone (97) offerLoc ((-42.8477, 147.2959)) endReason (Same Zone - Waiting Longest) jobId (0) jobSentDate (null) jobBookedDate (null) jobPickupDate (null) jobCompleteDate (null));, 6024644 ([BookingHistory] ID (28612965) status (BOOKED) offerId (9977373) when (2021-06-04 08:50:01.0) driverId (3001313) vehicleId (77) offerZone (97) offerLoc ((-42.8477, 147.2959)) endReason () jobId (7231092) jobSentDate (null) jobBookedDate (2021-06-04 08:50:04.0) jobPickupDate (null) jobCompleteDate (null));, 6024644 ([BookingHistory] ID (28613097) status (ENGAGED) offerId (9977373) when (2021-06-04 08:50:01.0) driverId (3001313) vehicleId (77) offerZone (97) offerLoc ((-42.8477, 147.2959)) endReason (Started with DIGITAL IO) jobId (7231092) jobSentDate (2021-06-04 08:50:05.0) jobBookedDate (2021-06-04 08:50:04.0) jobPickupDate (2021-06-04 08:57:26.0) jobCompleteDate (null));, 6024644 ([BookingHistory] ID (28613268) status (COMPLETED) offerId (9977373) when (2021-06-04 08:50:01.0) driverId (3001313) vehicleId (77) offerZone (97) offerLoc ((-42.8477, 147.2959)) endReason (Completed with DIGITAL IO) jobId (7231092) jobSentDate (2021-06-04 08:50:05.0) jobBookedDate (2021-06-04 08:50:04.0) jobPickupDate (2021-06-04 08:57:26.0) jobCompleteDate (2021-06-04 09:11:31.0));6024644Fri Jun 04 09:00:00 AEST 202199 PERONNE AVE, MOONAHLOVE smithHOBART DAY SURGERY 10 WARNEFORD ST, SOUTH HOBART50:01.08:57:26.011:31.0773001313
Sheet2
Cell Formulas
RangeFormula
C2C2=MID(A2,FIND("(false)",A2)+9,7)
D2D2=TRIM(LEFT(SUBSTITUTE(MID(A2,FIND("Pickup",A2)+8,99),"[",REPT(" ",99)),99))
E2E2=TRIM(LEFT(SUBSTITUTE(MID(A2,FIND(" GEO ",A2)+6,99),")",REPT(" ",99)),99))
F2F2=TRIM(LEFT(SUBSTITUTE(MID(A2,FIND(" Name ",A2)+7,99),")",REPT(" ",99)),99))
G2G2=TRIM(LEFT(SUBSTITUTE(MID(RIGHT(MID(A2,1,FIND("Attributes",A2)),99),FIND("GEO",RIGHT(MID(A2,1,FIND("Attributes",A2)),99))+5,99),")",REPT(" ",99)),99))
H2H2=TRIM(LEFT(SUBSTITUTE(MID(A2,FIND("when",A2)+20,99),")",REPT(" ",99)),99))
I2I2=TRIM(LEFT(SUBSTITUTE(MID(SUBSTITUTE(A2,"jobPickupDate (null)","x"),FIND("jobPickupDate",SUBSTITUTE(A2,"jobPickupDate (null)","x"))+27,99),")",REPT(" ",99)),99))
J2J2=TRIM(LEFT(SUBSTITUTE(MID(SUBSTITUTE(A2,"jobCompleteDate (null)","x"),FIND("jobCompleteDate",SUBSTITUTE(A2,"jobCompleteDate (null)","x"))+31,99),")",REPT(" ",99)),99))
K2K2=TRIM(LEFT(SUBSTITUTE(MID(SUBSTITUTE(A2,"vehicleId (0)","x"),FIND("vehicleId",SUBSTITUTE(A2,"vehicleId (0)","x"))+11,99),")",REPT(" ",99)),99))
L2L2=TRIM(LEFT(SUBSTITUTE(MID(SUBSTITUTE(A2,"driverId (0)","x"),FIND("driverId",SUBSTITUTE(A2,"driverId (0)","x"))+10,99),")",REPT(" ",99)),99))
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Create New Sheet with Specific Name Based on Variable Cell
Replies
0
Views
242
SBExcel123
S
P
  • Question Question
IF Function for RAG
2
Replies
10
Views
3K
PaulyK
P
M
  • Question Question
Help with FilterXML formula
Replies
0
Views
2K
montex01
M
SaraWitch
  • Solved
SUMPRODUCT from a range of cells, one of which contains a formula
Replies
5
Views
644
Fluff
Fluff
F
  • Question Question
Referencing multiple cells to return information that is contained in the same column
Replies
1
Views
751
fmoaveni
F

Forum statistics

Threads
1,224,823
Messages
6,181,175
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top