TRIM text

[Garetht2014]

Hi,

I have a spreadsheet with column that I need to get a specific piece of information out of each cell. The text has been put in line by line into each cell and I am trying to retrieve the line that starts with SNI (4th line from the bottom of the below list) and place that piece of text in another cell so all I can see in the cell is 'SNI:UG congested duct'. I have tried to TRIM the cell but then this essentially makes all the text one single line of text!

Is anyone able to help me out with this as I have nearly 3500 cells to go through and extract this information ?

ACC:
SiteAccessLocationDescription:
SiteAccessLocationWhat3Words:
IsParkingAvailableOn-Site?:N
ParkingLocationDescription:
ParkingLocationWhat3Words:
BTS:
SiteVisitNotes:
SiteContactName:

SiteContactSecondaryTelephone:SpecialArrangementNotes:
AccessPassword:
SiteContactEmail:
SiteContactTelephone:

HazardNotes:None
AltSiteContactName:
AltSiteContactTelephone:
AltSiteContactSecondaryTelephone:
AltSiteContactEmail:
CompanyName:
ProjectReference:
CriticalService:N
FIT:14/02/2023 16:21:00----------------------Engineer detailsEngineer name: Natalie,Walsh----------------------Ring ahead informationPrimary customer name: Contact noCall outcome:No answer (VM left if available)Call outcome to secondary contact no: Not able to ring ahead due to unusable contact details (missing / incorrect / faulty line)-------------------------------#Incomplete task summary#Access to network is required to complete the job. ----------------------#More details# Equipment needed - Other National grid to remove ivy from pole. - tel no Equipment location: Pole. Additional info : Ivy on pole A third party to provide is responsible for access. Additional info : National grid to remove ivy from pole. - tel no Location where access to network is needed : - [See map location----------------------#Work done#No work completed. ----------------------Notes from engineer:Roped from property to EL pole, rope under flagstone. Hoist required for road crossing as unable to cut and draw. National grid contacted to remove ivy from pole National grid Ref 22/02/2023 12:44:07-FTTPDESK, AMMeasured light level at CSP : -17.00 dbVoice wiring solution: Order Status - Not InstalledReason for not installing voice wiring solution: Order - Not Required Notes- Network provided to CSP - Emulated at desk no work completed. === Ring ahead information === Primary EU contact name : Primary Contact no : +What was the outcome of the call ? : Successful call Call date & time : 2023-02-22T12:42:00 === Ring ahead information ends ===
HAZ:None No further hazards are present for this task
HAZ:NoneNo further hazards are present for this task
INS:EUConsent-EU Access Granted
Estimated Lead-in cable length - 30-60
Notes Date and Time: 22/02/2023 12:44:08
STKXR16
AM FTTPDESKnull
22/02/2023 12:44:07-FTTPDESK, AM

Measured light level at CSP : -17.00 db
Voice wiring solution: Order Status - Not Installed
Reason for not installing voice wiring solution: Order - Not Required

Notes- Network provided to CSP - Emulated at desk no work completed.
=== Ring ahead information ===

Primary EU contact name :
Primary Contact no :
What was the outcome of the call ? : Successful call
Call date & time : 2023-02-22T12:42:00

=== Ring ahead information ends ===


CSP installation - COMPLETE
CBTCSP connectivity - COMPLETE


SAL:
SiteVisitNotes:
SiteContactName:
SiteContactSecondaryTelephone:+
SpecialArrangementNotes:
AccessPassword:
SiteContactEmail:
SiteContactTelephone:
HazardNotes:None
AltSiteContactName:
AltSiteContactTelephone:
AltSiteContactSecondaryTelephone:
AltSiteContactEmail:
CompanyName:
ProjectReference:
CriticalService:N
SNI:UG congested duct
SUR:Survey Notes:
TSK:Generic Ethernet Access - FTTP
WAR:Currently there are no known Warnings for this task

 

[etaf]

how about
=INDEX(A:A,MATCH("SNI:"&"*",A:A,0))

what version of excel do you have

you may have FiLTER
and could use

=FILTER($A$2:$D$10000,LEFT($A$2:$A$10000,LEN(H2))=H2,"No Results!")
Where you put SNI into cell h2

Book5
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	ACC:					INDEX			FILTER
2	SiteAccessLocationDescription:					SNI:UG congested duct		SNI	SNI:UG congested duct	0	0	0
3	SiteAccessLocationWhat3Words:
4	IsParkingAvailableOn-Site?:N
5	ParkingLocationDescription:
6	ParkingLocationWhat3Words:
7	BTS:
8	SiteVisitNotes:
9	SiteContactName:
10
11	SiteContactSecondaryTelephone:SpecialArrangementNotes:
12	AccessPassword:
13	SiteContactEmail:
14	SiteContactTelephone:
15	123sni456
16	HazardNotes:None
17	AltSiteContactName:
18	AltSiteContactTelephone:
19	AltSiteContactSecondaryTelephone:
20	AltSiteContactEmail:
21	CompanyName:
22	ProjectReference:
23	CriticalService:N
Sheet1

Cell Formulas
Range	Formula
F2	F2	=INDEX(A:A,MATCH("SNI:"&"*",A:A,0))
I2:L2	I2	=FILTER($A$2:$D$10000,LEFT($A$2:$A$10000,LEN(H2))=H2,"No Results!")
Dynamic array formulas.

 

[Garetht2014]

Thanks for those ideas. I have tried them but using the 'FILTER' formula I get 'No Results' & using the 'NDEX' I get '#N/A'.

I notice that in your spreadsheet above you have each line of text in it's own row, but all my lines of text are in one cell. Would this be why the formula isn't working ?

I have changed the formula to match my columns. This is what I have:

FTTP - South Wales 06-03-2023 10-06-02 -TEST.xlsx
	J	K	L
1	Job Notes	Column1	Column2
2	ACC: SiteAccessLocationDescription: SiteAccessLocationWhat3Words: IsParkingAvailableOn-Site?:N ParkingLocationDescription: ParkingLocationWhat3Words: BTS: SiteVisitNotes: SiteContactName: SiteContactSecondaryTelephone:+ SpecialArrangementNotes: AccessPassword: SiteContactEmail: SiteContactTelephone:+ HazardNotes:None AltSiteContactName: AltSiteContactTelephone: AltSiteContactSecondaryTelephone: AltSiteContactEmail: CompanyName: ProjectReference: CriticalService:N FIT:14/02/2023 16:21:00----------------------Engineer detailsEngineer name: ----------------------Ring ahead informationPrimary customer name:JOHN DAVIESContact no:+ outcome:No answer (VM left if available)Call outcome to secondary contact no: Not able to ring ahead due to unusable contact details (missing / incorrect / faulty line)-------------------------------#Incomplete task summary#Access to network is required to complete the job. ----------------------#More details# Equipment needed - Other National grid to remove ivy from pole. - Ref - tel no Equipment location: Pole. Additional info : Ivy on pole A third party to provide is responsible for access. Additional info : National grid to remove ivy from pole. - Ref - tel no Location where access to network is needed : - [See map location] ----------------------#Work done#No work completed. ----------------------Notes from engineer:d Ref 22/02/2023 12:44:07-FTTPDESK, AMMeasured light level at CSP : -17.00 dbVoice wiring solution: Order Status - Not InstalledReason for not installing voice wiring solution: Order - Not Required Notes- Network provided to CSP - Emulated at desk no work completed. === Ring ahead information === Primary EU contact name : Primary Contact no : + What was the outcome of the call ? : Successful call Call date & time : 2023-02-22T12:42:00 === Ring ahead information ends === HAZ:None No further hazards are present for this task HAZ:NoneNo further hazards are present for this task INS:EUConsent-EU Access Granted Estimated Lead-in cable length - 30-60 Notes Date and Time: 22/02/2023 12:44:08 AM FTTPDESKnull 22/02/2023 12:44:07-FTTPDESK, AM Measured light level at CSP : -17.00 db Voice wiring solution: Order Status - Not Installed Reason for not installing voice wiring solution: Order - Not Required Notes- Network provided to CSP - Emulated at desk no work completed. === Ring ahead information === Primary EU contact name : Primary Contact no : What was the outcome of the call ? : Successful call Call date & time : 2023-02-22T12:42:00 === Ring ahead information ends === CSP installation - COMPLETE CBTCSP connectivity - COMPLETE SAL: SiteVisitNotes: SiteContactName: SiteContactSecondaryTelephone:+ SpecialArrangementNotes: AccessPassword: SiteContactEmail: SiteContactTelephone:+ HazardNotes:None AltSiteContactName: AltSiteContactTelephone: AltSiteContactSecondaryTelephone: AltSiteContactEmail: CompanyName: ProjectReference: CriticalService:N SNI:UG congested duct SUR:Survey Notes: TSK:Generic Ethernet Access - FTTP WAR:Currently there are no known Warnings for this task	No Results!	SNI
3	ACC: SiteAccessLocationDescription: SiteAccessLocationWhat3Words: IsParkingAvailableOn-Site?:N ParkingLocationDescription: ParkingLocationWhat3Words: BTS: SiteVisitNotes: SiteContactName: SiteContactSecondaryTelephone: SpecialArrangementNotes: AccessPassword: SiteContactEmail: SiteContactTelephone: HazardNotes:NONE AltSiteContactName: AltSiteContactTelephone: AltSiteContactSecondaryTelephone: AltSiteContactEmail: CompanyName: ProjectReference: CriticalService:N HAZ:NONE Important - please follow the link to the INFORMe Safety Hazards and Warnings application and NewSite SharePoint to assess potential risks before travelling to site. HAZ:NONEImportant - please follow the link to the INFORMe Safety Hazards and Warnings application and NewSite SharePoint to assess potential risks before travelling to site. INS:EUConsent-EU Access Granted Estimated Lead-in cable length - <30 Notes Date and Time: SAL: SiteVisitNotes: SiteContactName: SiteContactSecondaryTelephone: SpecialArrangementNotes: AccessPassword: SiteContactEmail: SiteContactTelephone: HazardNotes:NONE AltSiteContactName: AltSiteContactTelephone: AltSiteContactSecondaryTelephone: AltSiteContactEmail: CompanyName: ProjectReference: CriticalService:N SNI:OH Feed with no anticipated issues SUR:Survey Notes: TSK:Generic Ethernet Access - FTTP WAR:Important - please follow the link to the INFORMe Safety Hazards and Warnings application and NewSite SharePoint to assess potential risks before travelling to site.	No Results!	SNI
8	ACC: SiteAccessLocationDescription: SiteAccessLocationWhat3Words: IsParkingAvailableOn-Site?:N ParkingLocationDescription: ParkingLocationWhat3Words: BTS: SiteVisitNotes: SiteContactName: SiteContactSecondaryTelephone: SpecialArrangementNotes: AccessPassword: SiteContactEmail: SiteContactTelephone: HazardNotes:NONE AltSiteContactName: AltSiteContactTelephone: AltSiteContactSecondaryTelephone: AltSiteContactEmail: CompanyName: ProjectReference: CriticalService:N FIT:03/01/2023 20:38:07-FTTPDESK, AGNotes- I cannot complete this task because a planned solution is required. There is existing line plant but I am unable to use because Alternate solution required. The TST slip number is NA.Alternate Solution required. an A55 has not been submitted because NA. QUOTED CBT NOT USEABLE REQUIREs planning of new cbt on dp84, Span from quoted cbt to address is over 112m and not policy. It cant be spanned between poles either and even if it could it should not be done. === Ring ahead information === Primary EU contact name : Primary Contact no : What was the outcome of the call ? : Successful call Call date & time : 2023-01-03T20:37:00 === Ring ahead information ends === 19/01/2023 03:21:36-FTTPDESK, AHMeasured light level at CSP : -17.00 dbVoice wiring solution: Order Status - Not InstalledReason for not installing voice wiring solution: Order - Not Required Notes- Network provided to CSP === Ring ahead information === Primary EU contact name : Davies Primary Contact no : What was the outcome of the call ? : Successful call Call date & time : 2023-01-19T03:20:00 === Ring ahead information ends === 23/01/2023 15:32:35-FTTPDESK, AMBattery backup unit: Status - Not SuppliedReason for not installing voice wiring solution: Order - Not Required Notes- I cannot complete this task because a planned solution is required. There is existing line plant but I am unable to use because As per survey officers notes new CBT reqd. The TST slip number is na.As per survey officers notes new CBT reqd. an A55 has not been submitted because esurvey raised. eSurvey already raised under the reference exchange PTB and (assoc order for neighbour) === Ring ahead information === Primary EU contact name : Primary Contact no : What was the outcome of the call ? : Successful call Call date & time : 2023-01-23T15:28:00 === Ring ahead information ends === AdditionalSpansQty:0, Completed With Hoist Support:0Knocked customers house and also phoned and no answer on either. GEN:***NOTE TO ENG: This is a BUILD TO THE WALL CP ENGINEER task, please attempt to contact the customer to confirm arrival but work can still be completed if contact is unsuccessful. Authorisation to complete basic external work has already been given by the customer (permission to drill, fit Tetra, attach cables and CSP to their property etc). A Power App must be submitted whether successful or not. If job complete perform Right Light check, update SNN and submit imagesThis is done on behalf of TipTop or Frames Pinning Tool HAZ:NONE No further hazards are present for this task HAZ:NONENo further hazards are present for this task INS:EUConsent-EU Access Granted Estimated Lead-in cable length - 61-150 Notes Date and Time: 23/01/2023 15:32:36 STKXR16 AM FTTPDESKnull Notes: 23/01/2023 15:32:35-FTTPDESK, AM Battery backup unit: Status - Not Supplied Reason for not installing voice wiring solution: Order - Not Required Notes- I cannot complete this task because a planned solution is required. There is existing line plant but I am unable to use because As per survey officers notes new CBT reqd. The TST slip number is na.As per survey officers notes new CBT reqd. an A55 has not been submitted because esurvey raised. eSurvey already raised under the reference exchange PTB and (assoc order for neighbour) === Ring ahead information === Primary EU contact name : Primary Contact no : What was the outcome of the call ? : Successful call Call date & time : 2023-01-23T15:28:00 === Ring ahead information ends === ONT installation - INCOMPLETE SAL: SiteVisitNotes: SiteContactName: SiteContactSecondaryTelephone: SpecialArrangementNotes: AccessPassword: SiteContactEmail: SiteContactTelephone: HazardNotes:NONE AltSiteContactName: AltSiteContactTelephone: AltSiteContactSecondaryTelephone: AltSiteContactEmail: CompanyName: ProjectReference: CriticalService:N SNI:OH Feed potential Line of sight problems SUR:Survey Notes: TSK:Generic Ethernet Access - FTTP WAR:Currently there are no known Warnings for this	#N/A
FTTP - South Wales

Cell Formulas
Range	Formula
K2:K3	K2	=FILTER($J$2:$J$10000,LEFT($J$2:$J$10000,LEN(L2))=L2,"No Results!")
K8	K8	=INDEX(J:J,MATCH("SNI:"&"*",J:J,0))

Cells with Data Validation
Cell	Allow	Criteria
J2:J8	Text length	<=20000

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Garetht2014]

Thanks Fluff.

It is 365 and I have now updated my account details

 

[Fluff]

Thanks for that.
How about

="SNI:"&TEXTBEFORE(TEXTAFTER(J2,"SNI:"),CHAR(10))

 

[Peter_SSs]

If "SUR:" always follows "SNI:" as in your examples, and you don't have TEXTBEFORE/TEXTAFTER then you could also try this

=REPLACE(LEFT([@[Job Notes]],FIND("SUR:",[@[Job Notes]])-2),1,FIND("SNI:",[@[Job Notes]])-1,"")