How copy a specific type of characters from a middle of cell to new cell
Hi,
i tried google but can't find right formula to extract specific characters out of a cell.
Kindly look at example below
"Address - Apt # 80, Address line , Area Name Street Mob: 0000-0000000
H/No. 586 Survey No. 627 Area : Full Name Distt : Distt Name: City"
I want to copy contact numbers starting from "Mob: 0000-0000000" from every cell ( more then 800 rows).
I hope i am in right place....
Last edited by Nicolass; 11-13-2017 at 10:07 AM. Reason: info
Hello and welcome to the forum.
We are likely going to need more than one example to get you to a solution that works for all instances.
That being said, what is the desired result that you are looking for based on your example? Is it "Mob: 0000-0000000", "0000-0000000", or something else entirely?
Hi, You can use the below formula if "Mob: 0000-0000000" = 17 digits & same in all rows.
=MID(A1,FIND("Mob",A1),17)
Regards,
Thangavel D
Appreciate the help? CLICK *
Thank you for helping out...
i want my result in this "0000-0000000" format. Any solution to find numbers in this format "0000-0000000" ?
I have a column with more then 1000 rows and every cell in that column contains text like this
"Address - Apt # 80, Address line , Area Name Street Mob: 0000-0000000
H/No. 586 Survey No. 627 Area : Full Name Distt : Distt Name: City"
Try this:
=MID(A1,SEARCH("Mob:",A1)+5,12)
Last edited by 63falcondude; 11-16-2017 at 10:35 AM. Reason: Typo
example in attachmentexcel.png
it doesn't work with all the cells.
as u can see in example.
In posts 1 and 4, you said that there was "Mob" in the text string. Now in post #6, you are showing that this will not always be the case.
As mentioned in post #2, we are likely going to need more than one example to get you to a solution that works for all instances.
Please upload a small representative sample of your data (not a picture).
Make sure there is just enough data to demonstrate your need.
Remember to desensitize the data.
Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
Ok i have attached excel file...
I want to just copy numbers snice these numbers are all in same format. Like (Network Code - Number )
Last edited by Nicolass; 11-16-2017 at 11:59 AM.
This works for the samples that you shared:
B1 =MID(A1,FIND(" 0",A1)+1,12)
Yes its working but it only copy one contact number, not the second contact number if there is one... as you can see in my attached example i have 2 contact numbers in a cell.
Last edited by Nicolass; 11-16-2017 at 12:08 PM.
That is because you never mentioned anything about extracting a second number. We can't read your mind.
I think that a VBA solution will suit this more than a formulaic one. Hopefully someone else will be able to help you with that.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks