Extract Variable Length Text Separated by Dashes ??

  • Thread starter Thread starter Legacy 300212
  • Start date Start date
L

Legacy 300212

Guest
i have been reading the topics that contain the phrase "extract text" and as a newbie am having trouble finding one that works for me

i am a stamp collector and keep a spreadsheet of my stamps

i enter the image id, number and condition separated by dashes into the column named "Image ID" and then break that data out manually and put the information in adjoining columns "Country", "Catalog", "Condition"


[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Image ID[/TD]
[TD]Country[/TD]
[TD]Catalog[/TD]
[TD]Condition[/TD]
[/TR]
[TR]
[TD]peru-209-used.jpg[/TD]
[TD]peru[/TD]
[TD]209[/TD]
[TD]used[/TD]
[/TR]
[TR]
[TD]switzerland-b10-mint.jpg[/TD]
[TD]switzerland[/TD]
[TD]b10[/TD]
[TD]mint[/TD]
[/TR]
[TR]
[TD]greatbritain-4155-mint.jpg[/TD]
[TD]great britain[/TD]
[TD]4155[/TD]
[TD]mint[/TD]
[/TR]
</tbody>[/TABLE]







is there some way to do this with a text function so when i paste the data in the "Image ID" column i can just fill it automatically into the others?


  • please note that many countries have a space between their names and vary from quite short to fairly long,
  • catalog numbers sometimes include letters and are between 1 and about 6 characters long
  • condition is always either used, mint or MNH

if there is a better way to initially name the image that makes extraction easier, i can change (i must name them manually) to any string i want that has the country-catalog-condition, like, if using / or _ is better than dashes, i can do that

any help would of course be appreciated
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
One possible way:


Excel 2010
ABCD
1Image IDCountryCatalogCondition
2peru-209-used.jpgperu209used
3switzerland-b10-mint.jpgswitzerlandb10mint
4greatbritain-4155-mint.jpggreatbritain4155mint
Sheet1
Cell Formulas
RangeFormula
B2=LEFT(A2,FIND("-",A2)-1)
C2=MID(A2,FIND("-",A2)+1,FIND("/",SUBSTITUTE(A2,"-","/",2))-FIND("-",A2)-1)
D2=SUBSTITUTE(MID(A2,FIND("/",SUBSTITUTE(A2,"-","/",2))+1,LEN(A2)),".jpg","")
 
Upvote 0
Another way:
Excel Workbook
ABCD
1Image IDCountryCatalogCondition
2peru-209-used.jpgperu209used
3switzerland-b10-mint.jpgswitzerlandb10mint
4greatbritain-4155-mint.jpggreatbritain4155mint
Sheet5
 
Upvote 0
wow ..
i googled "best excel help foum"
and someone said the "mr. excel" was the only place to go
they were right
many thanks guys
both work perfectly

let me push my luck ..

if i named an image that i used to name greatbritain-123-used.jpg
to great_britain-4155-mint.jpg (or use a dash or ?)

is there some way to return the country with a space between the "great and britain"

i don't want to have any spaces in image names because photo-hosts do funny things with the spaces
 
Upvote 0
yes, and i have heard that are "invisible spaces" or something
the server sees it as a character but excel sees it as a space ??
 
Upvote 0

Forum statistics

Threads
1,223,192
Messages
6,170,647
Members
452,344
Latest member
LarryRSch

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