How to use LEFT/RIGHT function in big data?

[Atul Rajratna]

Hi,

I'm quite confused with this. can we get the perticuler number/text from big data using LEFT/RIGHT formula?
----------------------------------------------------------------------
e.g.-
Verhuizing aanvraag Verhuizing per 19-05-2014
Datum aangemaakt : 05-05-2014 17:25:23
Regio : Brabant
Klantnummer : 98765432
Aanvraag : Backoffice
Geauthenticeerd : ja
Achternaam : Poyk
Initialen : T P
Voornaam :
Aanhef : dhr
Mobiel telefoonnummer : 0654987321
E-mailadres : T.Poyk99@ZMAIL.IN
Geboortedatum : 15-10-1958
Gegevens huidige adres
Straat : BURT
Huisnummer : 16
Postcode : 8801AV
Plaats : VODATELCO
Gegevens nieuw adres
Straat : PETRIACKY
Huisnummer : 62
Postcode : 4698AA
Plaats : DOCOUNIJ
Producten die niet kunnen worden meeverhuisd :
Bestaande producten meeverhuisd naar nieuwe adres : Alles-in-1 Power pakket, Digitale Kabel TV en Radio, Gebruikskosten extra CI+ Module, Bellen, 120 Mbps Internet, Starter
----------------------------------------------------------------------

This is the data I have in cell A1 only. I want second Huisnummer (from the bold section) i.e - "62" and Postcode i.e - "4698AA" combined in cell C1 like "4698AA 62"
Again the another problem is sometime Huisnummer comes with 4/5 digits (e.g-9851AB)

I tried to do this but getting first Huisnummer & Postcode.

Please help me to solve this problem. Thank you so Much!

-Atul Rajratna

 

[Luke M]

Not with LEFT and RIGHT, but with MID and SUBSTITUTE. Substitute lets us choose which instance of a word we want to replace, which is important for your question. Basically, we'll look for boundary words, replace them with large gaps, and then cut out the section we want. Overall formula:
=TRIM(MID(SUBSTITUTE(SUBSTITUTE(A1,"Plaats",REPT(" ",999),2),"Postcode :",REPT(" ",999),2),999,999))&" "&
TRIM(MID(SUBSTITUTE(SUBSTITUTE(A1,"Huisnummer :",REPT(" ",999),2),"Postcode :",REPT(" ",999),2),999,999))

 

[Atul Rajratna]

OH MA GOSH!

What a exact solution sir.. Lovely! Thank you so much Luke M sir.

Thank you ssssssssssso much!