Simple Data Extractiom

richardthelionheart

Board Regular
Joined
Mar 25, 2016
Messages
95
Office Version
  1. 2016
Platform
  1. Windows
Dark Aero Form: 31
Alien Loren Form: TTTTT
Cairns Lulu Form: T3166
Shellam Larry Form: 415
Madabout Molly Form: 55161
Cashen Flo Form: 3316T

Let's say this data is in column A rows 1 - 6
In column B rows 1 - 6 I want to see -
Form: 31
Form: TTTTT
Form: T3166
Form: 415
Form: 55161
Form: 3316T

So ............
the only ' constant' is Form: (which I want to retain)
I'm sure this is fairly simple but I could take hours figuring out ' ' search, ' find " ???
Any help greatly appreciated.


<colgroup><col style="mso-width-source:userset;mso-width-alt:8338;width:171pt" width="228"> </colgroup><tbody>
</tbody>
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Try:
Code:
=TRIM(MID(A1,FIND("Form:",A1)+5,LEN(C2)))
 
Last edited:
Upvote 0
You're welcome and thank you, actually a bright sunny day today! Though I'm sure warmer for you right now :)
 
Upvote 0
Thanks, Jack it works well.
:unsure: Really? What does LEN(C2) have to do with data in A1:A6?
Also, you said you wanted to retain "Form:". The given formula does not do that.


In column B rows 1 - 6 I want to see -
Form: 31
Form: TTTTT
Form: T3166
Form: 415
Form: 55161
Form: 3316T
My suggestion is shown in B1 (copied down). For comparison, I have included the previously suggested formula in D1 (copied down). The column is blank!

Excel Workbook
ABCD
1Dark Aero Form: 31Form: 31 
2Alien Loren Form: TTTTTForm: TTTTT
3Cairns Lulu Form: T3166Form: T3166
4Shellam Larry Form: 415Form: 415
5Madabout Molly Form: 55161Form: 55161
6Cashen Flo Form: 3316TForm: 3316T
Form
 
Last edited:
Upvote 0
Well, Peter, you are in fact right. But then you are a moderator so that would be expected I guess. I chose to go down the 'diplomatic path' as it were, and with a bit of tweeking I made the formula work. (ie the original one). I fully understand that in the world of mathematics and excel formulas things are either right or wrong. I realised that the Form: component had disappeared in the original formula and decided, on reflection, that I could just insert a new column with Form: and the adjacent column with the numbers and digits. I also understand that your post was just you doing your 'job' it I can put it that way. Thank you.
 
Upvote 0
No problem. My comments were as much for other future readers (who might not be able to make the required 'tweaks') as for you. Anyway, you now have choices. :)
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top