Wildcards

[Richard Davies]

Hi,

Does Xl or VB have a wildcard for numerical data, and is there a different wildcard for all text data.

I know of * and ? wildcards but these are brutal in their application as they make no distinction between alpha and numerical charcters.

Any help is much appreciated.

Best regards
Richard

[daddylonglegs]

Hello Richard,

not sure about VB but there are only those wildcards for use in worksheet functions. Do you have a specific problem?

[Richard Davies]

Hi Daddylonglegs,

My specific problem is:

If I have a single column consisting of say 20,000 lines, each cell contains a variable length string, which begins with variable length text (including spaces) and is followed by variable legth numbers which are space delimited.

A small example of this data is shown below:
Turnover 100,000 50,000
Costs of sales (30,000) (15,000)
Gross profits 70,000 35,000

Question: How to normalise this data into 3 columns. The first column containing the description, the second column the number, the third column the next number along in the string.

If I run "text text to columns" and used delimited by space, then xl puts a column in after the word "costs" and "of" and "sales". So I end up with unusable data, which is not all aligned in the right columns.
Clearly I do not want to use "format as fixed width" as this would be a manual job and take ages.

Any ideas?

I know there is a * wildcard in XL and also ? is used, but in this example these two wildcards do not help me.
Is there a wildcard in XL for numerical data (like #), and text data.

[daddylonglegs]

You might be able to use this approach, assuming your data is in A1 down then use these 3 formulas in B1, C1 and D1 and copy down the columns.

=TRIM(LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9,"("},A1&"0123456789("))-1))

=LEFT(TRIM(SUBSTITUTE(A1,B1,"")),FIND(" ",TRIM(SUBSTITUTE(A1,B1,"")))-1)

=TRIM(SUBSTITUTE(SUBSTITUTE(A1,B1,""),C1,""))

[daddylonglegs]

Note: the above approach will make the numerical values "text formatted" which might cause you problems if you want to use these for calculations. to make columns C and D numeric change the last 2 formulas to

=LEFT(TRIM(SUBSTITUTE(A1,B1,"")),FIND(" ",TRIM(SUBSTITUTE(A1,B1,"")))-1)+0

and

=TRIM(SUBSTITUTE(SUBSTITUTE(A1,B1,""),TEXT(C1,"#,###;(#,###);0"),""))+0

and then you can format these columns any way you want (including using brackets for negative numbers)

[Richard Davies]

Nice one Daddy

Looks like this would work, although it doesn't look a particularly elegant solution.

Are there any other ways to sort this?

Thanks for the help.
Best regards
Richard

[Richard Davies]

Hi Daddylonglegs.

I have had another potential solution by solnajeff, but on comparison yours seems most robust.

Thank your valuable advice.

Best regards
Richard