Parse text with different variables

[Calicarrie]

I am trying to parse some text values into 2 cells based on the "Name" and the "Size". Examples include:

PORT750ml/4pk
CHARDONNAY3L/o
WINE1.5L

Which would read as (separate cells):
PORT 750ml/4pk
CHARDONNAY 3L/o
WINE 1.5L

Since the "size" is variable and the "name" is not of consistent length, I am not sure how to automate this (preferably without a macro). I tried a "Left" function with "find" but the find function cannot include wildcards and the size is not standard.

Any ideas would be greatly appreciated!

Thanks!

 

[Desu Nota from Columbus]

I've been trying to figure this out, but I am stuck

I tried this;
=MID(A1,1,(FIND(OR(0,1,2,3,4,5,6,7,8,9),A1,1))-1)

Didn't work!

But this works:
=MID(A1,1,(FIND(3,A1,1))-1)
CHARDONNAY3L/o ---> CHARDONNAY

and:
=RIGHT(A1,LEN(A1)-(FIND(3,A1,1))+1)
CHARDONNAY3L/o ---> 3L/o




I need to know how to say, " Find any of the following values and return their position within the string"!!!!!!!!!!!!!!!!! Find({0,1,2,3,4,5,6,7,8,9}), i tried using this but I can't get it to work either.

 

[Scott Huish]

Incorrect Answer

 

[Desu Nota from Columbus]

Does this spark any ideas? This is driving me bonkers.........................

 

[Scott Huish]

Look above your post!

 

[Desu Nota from Columbus]

HOTPEPPER:

Can you explain the Replace function you used?

I don't quite grasp the part after the MIN(Find(..............), especially the concatenated value.

 

[Scott Huish]

I'm glad you said something, I made a small mistake in that, and then I'll explain it.

Excel Workbook
	A	B	C
1	PORT750ml/4pk	PORT	750ml/4pk
2	CHARDONNAY3L/o	CHARDONNAY	3L/o
3	WINE1.5L	WINE	1.5L
Sheet1

 

[Scott Huish]

This is going to create an array of every position it finds a number in the string. The concatenation is to ensure that it finds every number, so as to not get #VALUE!
Min is used so that the lowest position that a number is found (the first position) is the position -1 that we are going to replace with nothing.

So starting at the 1st character to the point it found the first number -1, it will replace with nothing. This gives us the second part.

The first part is just a basic LEFT formula that utilizes the length of the second part.

 

[Desu Nota from Columbus]

This is going to create an array of every position it finds a number in the string. The concatenation is to ensure that it finds every number, so as to not get #N/A

Min is used so that the lowest position that a number is found (the first position) is the position -1 that we are going to replace with nothing.

So starting at the 1st character to the point it found the first number -1, it will replace with nothing. This gives us the second part.

The first part is just a basic LEFT formula that utilizes the length of the second part.

I see the logic of this, and it is very clever---I wouldn't have ever thought to concatenate all numbers to avoid the #N/A;

Does this mean that FIND({0,1,2,3,4,5,6,7,8,9}) searches for matches for this entire list and if even one number is not present, it will return an error? (for instance if you leave off the number 9 in the concatenation)

From this workaround, I am to believe that there is no direct way to say, "Search/Find the first position of any of the following values within a string"?

 

[Scott Huish]

Yes, it would return an error if that value was not in the string.