Extract from varied texts
I'm looking for a formula (complex, I think) that will do the following.
There are in 9 separate rows (cells) in Col A.(see entries below) I need to extract 2p-7p only into a cell in Col B. If Col A is blank,(row 5) then Col B should be as well.
x 2p-7p Op Canyon 1+4
x2 2p-7p
x2 2p-7p xfact
2p-7p pop Lazy 1+4
2p-7p
2p-7p Up
2p-7p Op Xray
"2p-7p"
Thank you in advance...
Formula for B1 =IFERROR(MID(A1,SEARCH("2p-7p",A1,1),5),"")
hope this will help
may be:Formula:
Please Login or Register to view this content.
or
may be opposite:Formula:Please Login or Register to view this content.
or ...
Will you please attach a SMALL sample Excel workbook(s) (10-20 rows of data is usually enough)? Please don't attach a picture of one or text inside a post (no-one will want to re-type all your stuff before starting).
1. Make sure that your sample data are truly REPRESENTATIVE of your real data. (If trere are typical cases like: all unique values/duplicates could occur, day/night, nobody present/several persons at once, before/on/past due, etc. - please show them all or at least indicate in text) The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.
2. Make sure that your desired solution(s) is/are also shown (mock up the results manually).
3. Make sure that all confidential/restricted information (either personal or business) like real e-mails, social security numbers, bank accounts, etc. is removed first!!
To attach an Excel file you have to do the following: Just before posting, scroll down and press Go Advanced button and then scroll down and press Manage Attachments link. Now follow the instructions at the top of that pop-up screen.
Best Regards,
Kaper
Re: Extract from varied texts
My apologies, the suggestion worked, however...
I did not fully represent the actual data variations for my problem. The info I need to extract changes from cell to cell.
I have uploaded an example which has been edited for privacy, but is a copy of actual types of data.
Thank you again for any help you can provide.
Hi,
For your uploaded sample, use this formula in C3 copied down:
Formula:Please Login or Register to view this content.
See attached.
Thank you so much...
Almost perfect...
If there is an entry of 7p-9:30p, the result is 7p-9:3 instead of 7p-9:30p. I do some further work with result, and 9:3 becomes 9:03 instead of 9:30
The rest seem to be working as I go through the various entries...
Those scenarios were Not included in your sample, my formula won't work for those.Originally Posted by rmexcell
If you need further help, please re-upload a sample with all possible string formats.
In F3 try:
Formula:Please Login or Register to view this content.
EDIT: It would likely help if you understood how the formulas worked. Here is a good explanation https://exceljet.net/formula/extract...-specific-text
be aware this formula assumes there isnt another "-" (without quotes) in the string, if there is it will not work.
Last edited by Zer0Cool; 08-29-2018 at 06:15 PM.
Ways to get help: Post clear questions, explain your overall goal, supply as much background as possible, respond to questions asked of you by those trying to help and post sample(s) files.
"I am here to help, not do it for people" -Me
Re: Extract from varied texts
I really appreciate the help...
Here is updated example file, see lines 12-15.
This updated formula works for All your samples in Post 9:
Formula:Please Login or Register to view this content.
See attached.
Well Done!!
I am grateful for the help. So far, so good as I implement into the master spreadsheet.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks