Ask Your Question
0

How to extract the name and address from mailing list cell in Calc [closed]

asked 2014-03-24 05:57:33 +0200

this post is marked as community wiki

This post is a wiki. Anyone with karma >75 is welcome to improve it.

I have data in libroffice calc like below

"V K RAJU
E -45  COLLEGE LANE
BENGALI MARKET
XYZ  -110001"
"B  PAVAN KUMAR SARMA
D.No: 4318/3,Ansari Road, Dariya Gunj,
XYZ - 110 002."

like upto 3500 records are there in that file. In that file how to extract the mailing list please tell me as early as possible

In that data mailing list means what i want to extract(either name or Address)

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-02-20 15:42:46.506160

1 Answer

Sort by » oldest newest most voted
1

answered 2014-03-24 09:00:41 +0200

oweng gravatar image

updated 2014-03-24 11:04:32 +0200

If the first line is always the name (and only the name), this will obtain the name:

=TRIM(LEFT(A1;SEARCH(".\n";A1;1)))

The remaining lines of content (address) can be obtained using:

=REPLACE(TRIM(MID(A1;SEARCH("\n[.]*";A1;1);LEN(A1)-SEARCH("\n";A1;1)));1;1;"")

Example here.

edit flag offensive delete link more

Comments

it is not work well because in libreoffice there is no " so please modify your answer

Ram.BM gravatar imageRam.BM ( 2014-03-24 10:24:39 +0200 )edit
1

@Ram.BM, I left out a bracket in the address formula. Now corrected and example attached.

oweng gravatar imageoweng ( 2014-03-24 11:05:26 +0200 )edit

Thanq it works now exactly thanq

Ram.BM gravatar imageRam.BM ( 2014-03-24 12:27:13 +0200 )edit

Question Tools

1 follower

Stats

Asked: 2014-03-24 05:57:33 +0200

Seen: 659 times

Last updated: Mar 24 '14