Extract Variable Length Text Separated by Dashes ??

[Legacy 300212]

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"

Image ID	Country	Catalog	Condition
peru-209-used.jpg	peru	209	used
switzerland-b10-mint.jpg	switzerland	b10	mint
greatbritain-4155-mint.jpg	great britain	4155	mint

<tbody>
</tbody>

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

 

[bschwartz]

One possible way:

Excel 2010
	A	B	C	D
1	Image ID	Country	Catalog	Condition
2	peru-209-used.jpg	peru	209	used
3	switzerland-b10-mint.jpg	switzerland	b10	mint
4	greatbritain-4155-mint.jpg	greatbritain	4155	mint
Sheet1

Cell Formulas
Range	Formula
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","")

 

[JoeMo]

Another way:

Excel Workbook
	A	B	C	D
1	Image ID	Country	Catalog	Condition
2	peru-209-used.jpg	peru	209	used
3	switzerland-b10-mint.jpg	switzerland	b10	mint
4	greatbritain-4155-mint.jpg	greatbritain	4155	mint
Sheet5

 

[Legacy 300212]

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

 

[FDibbins]

You could probably use Find/Replace for that...
Find _
Replace (space)

 

[Legacy 300212]

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 ??

 

[bschwartz]

Try this formula in cell B2.

=SUBSTITUTE(LEFT(A2,FIND("-",A2)-1),"_"," ")

 

[Legacy 300212]

Try this formula in cell B2.

=SUBSTITUTE(LEFT(A2,FIND("-",A2)-1),"_"," ")

yes! ... pin gold start to chest

thanks everyone very much

 

[bschwartz]

You're very welcome.