how to remove text from a cell

[Fazlu]

A cell has the following details
"
STAR TRANSPORT AND LOADI PRIVATE CAR COMPREHENSIVEPolicy #:150200062 (Invoice : PDB0115010007) (01/0201/141455/00/00) TOYOTA HILUX :TBA
"

I want to get the number 1560200062 separably in a cell. how do i do that..please help. i have uploaded a sample file

 

[NARAYANK991]

Hi ,

Just one row of data is practically useless ; please upload a sample workbook with enough data in it , covering all possible variations.

Narayan

 

[Fazlu]

Hi ,

Just one row of data is practically useless ; please upload a sample workbook with enough data in it , covering all possible variations.

Narayan

There are 4000 rows.... due to confidentiality i cant post the whole..but i guess once i get a formula. i can just copy paste the formula to rest of the rows

 

[NARAYANK991]

Hi ,

Then confirm that all the rows of data follow the same structure.

Will all the numbers of interest be preceded by the following text :

Policy #:

Narayan

 

[Fazlu]

Hi ,

Then confirm that all the rows of data follow the same structure.

Will all the numbers of interest be preceded by the following text :

Policy #:

Narayan

All the rows are similar.... the characters in each row change as different rows have different names. uploaded a sample. hope this helps

 

[NARAYANK991]

Hi ,

Try this :

=MID(C3,FIND("Policy #:",C3) + LEN("Policy #:"),10)

Narayan

 

[Fazlu]

Hi ,

Try this :

=MID(C3,FIND("Policy #:",C3) + LEN("Policy #:"),10)

Narayan

Fantastic...thank you so much Mr. Narayan. Now the second leg of the report i thought the same formula would work. but it doesnt. can you check :D . i have uploaded sample

 

[Fazlu]

Hi ,

Try this :

=MID(C3,FIND("Policy #:",C3) + LEN("Policy #:"),10)

Narayan

sorry Mr. Narayan. can you help me with this as well

 

[Chihiro]

Same principle. First find position of where "PI.No " ends +1 to find starting number. Then length of 10 char from that number.
=MID(C11,LEN("PI.NO ")+1,10)

P.S. The difference is that FIND() returns position of first character in string in text, therefore you need to add length of the string to Find() result. But in your second request, "PI.No " is at start of string, so use LEN() which returns length of the string +1 will give you where the number starts.

 

[Fazlu]

Same principle. First find position of where "PI.No " ends +1 to find starting number. Then length of 10 char from that number.
=MID(C11,LEN("PI.NO ")+1,10)

P.S. The difference is that FIND() returns position of first character in string in text, therefore you need to add length of the string to Find() result. But in your second request, "PI.No " is at start of string, so use LEN() which returns length of the string +1 will give you where the number starts.

thank you so much