Extracting required strings from alpha numeric value in a row in excel

**piggyfox** · 05-29-2013, 05:53 AM

Greetings,
Being new and unable to get the exact function/macro from searching the forums posting this question I have and appreciate if get answer in a day (please..)

requirement is

the rows(hundreds of rows) has data like:- 2X20AA, 30AA, 1X40AA,6AA ( in say A1 )

[the actual data is ( 2*20AA, 30AA, 1|*40AA,6AA) written in the above format]

result should be
In B2:- 40 ( which is 2X20)
In C2:- 30
In D2:- 40 (which is 1X40)
In E2:-6

The total characters are not fixed in all rows but the data is written in the same format and seperated by 'comma'.
Thank you.

**ragulduy** · 05-29-2013, 06:14 AM

Can you upload an example workbook with some of the data in? Do the rows only have data in column A?

**jindon** · 05-29-2013, 06:18 AM

Need more examples...

UDF

B1
=splitby($A1,COLUMN(A1))
and copy right / down

To a standard module

Please Login or Register  to view this content.

**piggyfox** · 05-29-2013, 06:24 AM

@Yudlugar

giving here the some of the data i have in the sheet, and there is data in other rows. but if I can have the macro or function can that be used to get the data in any rows wanted ??
2x10NF, 2x70NF,6Nf, 3NF, 2NF
2x15NF, 2x50NF, 2x20NF
2x75NF, 2x5NF, 2x2NF, 2x10Nf, 6Nf
2x75NF, 2x5NF, 2x2NF, 2x10NF, 6NF
2x30NF, 4x10NF, 4x2Nf, 6NF

**ragulduy** · 05-29-2013, 06:36 AM

You can upload an attachment by clicking "go advanced" in the reply and then selecting attachments. It would be very helpful to see how the data is layed out in a spreadsheet.

**piggyfox** · 05-29-2013, 06:39 AM

thank you... corrected my question in .... didnt get the result i wanted with the function..

**jindon** · 05-29-2013, 06:41 AM

Originally Posted by piggyfox

thank you... corrected my question in .... didnt get the result i wanted with the function..

My post has been updated, due to your misleading examples in the first post...

**piggyfox** · 05-29-2013, 06:47 AM

Attaching the sheet with partial data.
there are two columns (C & G) with similar data and I need to do same function for both.

**jindon** · 05-29-2013, 06:55 AM

What is your desired result then?

**piggyfox** · 05-29-2013, 07:04 AM

@jindon

Attached the sheet again with giving the result iam expecting for 3 rows of data. Please refer to the columns I onwards
thank you.

**ragulduy** · 05-29-2013, 07:13 AM

I did this before you posted the result so it sticks the results in column A but I think it gives you the required answers.

Please Login or Register  to view this content.

**jindon** · 05-29-2013, 07:14 AM

I2:
=IFERROR(SplitBy($B2,COLUMN(A1)),"")
then copy right/down

Q2:
=IFERROR(SplitBy($G2,COLUMN(A1)),"")
then copy right/down

Should do.

Typo

**piggyfox** · 05-29-2013, 08:06 AM

Thank You Yudlugar & jindon
am trying for now the solution given by jindon and its giving the result am looking..
will also try the solution given by yudlugar later and will inform the result.
once again THANK YOU for responding with lightening speed with the solution.

**piggyfox** · 05-31-2013, 12:30 PM

@jindon another help required ...can the function be made to convert "x" irrespective of the case of "x" ?

**jindon** · 05-31-2013, 04:42 PM

Add one line

Please Login or Register  to view this content.

Extracting required strings from alpha numeric value in a row in excel

LinkBack

Thread Tools

Rate This Thread

Display

Extracting required strings from alpha numeric value in a row in excel

Re: Extracting required strings from alpha numeric value in a row in excel

Re: Extracting required strings from alpha numeric value in a row in excel

Re: Extracting required strings from alpha numeric value in a row in excel

Re: Extracting required strings from alpha numeric value in a row in excel

Re: Extracting required strings from alpha numeric value in a row in excel

Re: Extracting required strings from alpha numeric value in a row in excel

Re: Extracting required strings from alpha numeric value in a row in excel

Re: Extracting required strings from alpha numeric value in a row in excel

Re: Extracting required strings from alpha numeric value in a row in excel

Re: Extracting required strings from alpha numeric value in a row in excel

Re: Extracting required strings from alpha numeric value in a row in excel

Re: Extracting required strings from alpha numeric value in a row in excel

Re: Extracting required strings from alpha numeric value in a row in excel

Re: Extracting required strings from alpha numeric value in a row in excel

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions