splitting text to array,is it possible?

the text is seperated by "_", like("abc_xyz_spq") and is not set how
many "_"s there("abc_xyz_spq","xyz_spq","abc_xyz_spq_pqr" etc).
I want to do something to each text(abc,xyz,spq, etc).
How can I split them without VBA.
StringTokenizer-like function.
0
kang (41)
8/28/2007 12:14:41 PM
excel 39879 articles. 2 followers. Follow

5 Replies
827 Views

Similar Articles

[PageSpeed] 18

Data>Text to columns, choose Delimited, Next, check Other and fill in the underscore

-- 
Kind regards,

Niek Otten
Microsoft MVP - Excel

"kang" <kang@hao.com> wrote in message news:eM9BE0W6HHA.4880@TK2MSFTNGP03.phx.gbl...
| the text is seperated by "_", like("abc_xyz_spq") and is not set how
| many "_"s there("abc_xyz_spq","xyz_spq","abc_xyz_spq_pqr" etc).
| I want to do something to each text(abc,xyz,spq, etc).
| How can I split them without VBA.
| StringTokenizer-like function. 


0
nicolaus (2022)
8/28/2007 12:21:16 PM
As I said earlier, it's possible to know how many time the token repeats.
so I prefer to splitting them to array.

Niek Otten wrote:
> Data>Text to columns, choose Delimited, Next, check Other and fill in the underscore
> 
0
kang (41)
8/28/2007 12:45:26 PM
It will work for any number of underscores (as long as you don't exceed Excel's limit for the number of columns)

Or do you mean something else?

-- 
Kind regards,

Niek Otten
Microsoft MVP - Excel

"kang" <kang@hao.com> wrote in message news:e%23ynPFX6HHA.600@TK2MSFTNGP05.phx.gbl...
| As I said earlier, it's possible to know how many time the token repeats.
| so I prefer to splitting them to array.
|
| Niek Otten wrote:
| > Data>Text to columns, choose Delimited, Next, check Other and fill in the underscore
| > 


0
nicolaus (2022)
8/28/2007 12:54:39 PM
kang,

Here is a formula solution.

For a string in A2, with an underscore character in cell A1 (your delimiter value), enter this into 
cell B2

=SUBSTITUTE(A2,LEFT(A2,FIND($A$1,A2)-1)&$A$1,"")

and copy to C2:I2 - Note that this is eight columns, which I've assumed to be the maximum number of 
delimited strings, but you can increase or decrease as appropriate.

Then in J2, enter the formula

=IF(ISERROR(SUBSTITUTE(A2,LEFT(A2,FIND($A$1,A2)-1)&$A$1,"")),IF(ISERROR(A2),"",A2),SUBSTITUTE(A2,$A$1&SUBSTITUTE(A2,LEFT(A2,FIND($A$1,A2)-1)&$A$1,""),""))

and copy to K2:Q2 (again, 8 columns)

and K2:Q2 will have the individual strings.

Then copy B2:Q2 down to match your values in column A, and then hide columns B to I.

HTH,
Bernie
MS Excel MVP


"kang" <kang@hao.com> wrote in message news:eM9BE0W6HHA.4880@TK2MSFTNGP03.phx.gbl...
> the text is seperated by "_", like("abc_xyz_spq") and is not set how
> many "_"s there("abc_xyz_spq","xyz_spq","abc_xyz_spq_pqr" etc).
> I want to do something to each text(abc,xyz,spq, etc).
> How can I split them without VBA.
> StringTokenizer-like function. 


0
Bernie
8/28/2007 1:55:05 PM
If you really want the values contained in an array,
then....assuming the letters are arranged in triplets separated by 
underscores....

Try this:

With A1 containing the text to parse into an array

B1: 
=MID(SUBSTITUTE(A1,"_",""),(ROW($A$1:INDEX($A:$A,(LEN(A1)-LEN(SUBSTITUTE(A1,"_",""))+1),1))-1)*3+1,3)

If A1 contains abc_xyz_spq_pqr
the formula returns this array: {"abc";"xyz";"spq";"pqr"}

Is that what you're looking for?

***********
Regards,
Ron

XL2003, WinXP


"kang" wrote:

> the text is seperated by "_", like("abc_xyz_spq") and is not set how
> many "_"s there("abc_xyz_spq","xyz_spq","abc_xyz_spq_pqr" etc).
> I want to do something to each text(abc,xyz,spq, etc).
> How can I split them without VBA.
> StringTokenizer-like function.
> 
0
RonCoderre (135)
8/28/2007 2:20:04 PM
Reply:

Similar Artilces:

Possible to copy exch store to twin server?
Hi.... We are looking to virtualize one of our small Exchange servers. When we do that, we would prefere that the old could keep running until the virtual one is ready... When we virtualize the server it will also be given a new IP adresse... Is it possible to just stop the old server and copy all store files to the new one and then start it up again....? Sincerely Erling Christensen On Fri, 29 Jan 2010 10:16:11 +0100, ErlingC <ec@vucsyd.dk> wrote: >Hi.... > >We are looking to virtualize one of our small Exchange servers. > >When we do tha...

curving text #2
I'm making an oval sign for my business and the text must follow the curve of the oval. Can't seem to do it... Give Word Art a try. The "A" icon on the left side of the screen. -- Don - Publisher 2000 Vancouver, USA "Betty Boop" <Betty Boop@discussions.microsoft.com> wrote in message news:3DD80208-ABEF-45D5-B14A-B088E308CE88@microsoft.com... > I'm making an oval sign for my business and the text must follow the curve > of the oval. Can't seem to do it... I have the an example on my web site. Scroll way down http://msauer.mvps.org/wo...

multi-dimentional arrays
I'm using MS Visual C++ 2008 and i'm trying to use an array containing some complex information, but the compiler states an error: error C2440: 'Initializing': cannot convert from 'char (*)[17] to 'char *' my array is this: char* Arr=new char[184][17]; Arr[0][0]="1"; Arr[0][1]="Basion"; Arr[0][2]="Ba"; Arr[0][3]="Basio.jpg"; Arr[0][4]="Basio.avi"; Arr[0][5]="Ponto mais inferior do contorno anterior do foramen Magnum"; Arr[0][6]="False"; Arr[0][7]=""; Arr[0][8]=""; Arr...

Split tasks a splitting headache
Inherited another Project IMS. I am noticing right off that the previous scheduler has been starting tasks without their FS preds completing. Yeah, it does show up as a spit bar in the gantt chart. But, does anyone know a way to filter out just task that started before predecessor completion? I'm dealing with several thousand lines. Thanks. -- trailerpup ------------------------------------------------------------------------ trailerpup's Profile: http://forums.techarena.in/members/116596.htm View this thread: http://forums.techarena.in/microsoft-project/1290853.htm ...

Why can't I Export selected cells to tab-delimited text file?
This should be an easy thing to do. I'd like to set up a "refreshable" text export, in much the same way that you can set up a "refreshable" text import. Maybe I'm missing something, but it seems that Excel has little in the way of Export capability. Yes, I know there is a slightly more cumbersome way of doing this: copy - open text file - paste - close text file - click "yes" in save dialog. Lisa B. This macro is fairly simple to implement: Public Sub ExportSelectionAsDelimitedFile() Const sDELIM As String = vbTab Const sPATH As...

How to randomly split a whole dataset into two sub-dataset?
Hi, At your possible convenience, might anyone please kindly answer my question? Thank you very much. How to "RANDOMLY" split the whole data set (n=2000) into two sub dataset (n=1000; n=1000) in SPSS or Excel? Thank you very much. Please take care Caroline zencaroline <zencaroline@gmail.com> writes: > How to "RANDOMLY" split the whole data set (n=2000) into two sub > dataset (n=1000; n=1000) in SPSS or Excel? Create a new variable whose value is randomly distributed. Sort the data on this variable. Take the first 1000 cases ...

Invisible text in drop down parameter query
Hi Don't know if the subject is explanation or not for what Ive got but here goes I have been trying to build parameter queries with drop down list combo boxes using the following tutorial [url=http://www.fontstuff.com/access/acctut08.htm#dialog]http:// www.fontstuff.com/access/acctut08.htm#dialog[/url] my platform is Access 2003 all was going well until I run the form with combo boxes, I can run query OK but when I choose from my drop-down lists no text is visible until I click in the list and then the selection is visible, I tough this must just be a font color problem, but I cant seem...

Separating left and right columns of text evenly
I'd like to use Publisher to re-do a menu. How do I keep the menu prices in an evenly spaced column to the right side of the text box? It would be similar to creating a table of contents, perhaps? -- Bill a table would probably be the easiest -- ~~~~~~~~~~~~~~~~~~ Rob Giordano Microsoft MVP Expression "Retired Chief" <RetiredChief@discussions.microsoft.com> wrote in message news:79B75A97-4ADD-48E9-A81D-DF527ECE922A@microsoft.com... | I'd like to use Publisher to re-do a menu. How do I keep the menu prices in | an evenly spaced column to the right side of th...

Join text
Ok. I will try to explain. I want to join text from several cells and I'm using a function "Sammafoga(A1;a2;a3)" But I want the text to be formatted in the recevied cell, i.e. font size from A1 shall be 18, a2 italic and a3 normal. How do I do that? tnx for your answer Robert -- Robert57 ------------------------------------------------------------------------ Robert57's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29838 View this thread: http://www.excelforum.com/showthread.php?threadid=495454 First copy the result of the formula and paste...

Conditional formatting for rows containing text
Is it possible to conditionally format for rows containing certain text? I receive a weekly report of hours and costs from our financial department that I need to sort and enter information into a spreadsheet for actuals vs. budget. This information comes to me with column A looking similar to this: 1234 Sum of Total Hours 1234 Sum of Total Cost 1235 Sum of Total Hours 1235 Sum of Total Cost 1236 Sum of Total Hours 1236 Sum of Total Cost 1237 Sum of Total Hours 1237 Sum of Total Cost with the #'s being different ch...

Multilevel Lists
I am trying to set up the format for a new multilevel list I want it set up so that: Level 1 text: Is ilays bold and black Level 2 text: Is always normal black Level 3 text: is walwys red Where do I set this at? It looks like in the formating for the list I can only edit the font style of the list numbers not the actual text after the numbers. Thanks Link each list level to a distinct style and define the font and paragraph formatting in the style. -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA http://word.mvps.org "Going...

Text box visibility
On my form "Finalise Jobs" i have (amongst other controls), "Date ready" - txtDateReady, "Date Required" - txtDateRequired, "WhyDelayed" - txtWhyDelayed, and a search Combo Box - cboSearch. The following code is attached to txtDateReady :- Private Sub txtDateReady_AfterUpdate() If txtDateReady > Forms![Finalise Jobs].txtDateRequired Then Me.lblWhyDelayed.Visible = True Me.txtWhyDelayed.Visible = True Else Me.lblWhyDelayed.Visible = False Me.txtWhyDelayed.Visible = False End If End Sub and the following...

IMPORTING TEXT DATA
I get emails from a service I use, it gives me client profiles in emails that vary in size and lenght (foregoing zonal OCR) the data in these unstructured emails I would like to extract are separated by semicolons as follows on different zones of the email: Company Name: Jones Furniture Name: Betty Jones Title: Office Manager Address: 123 Main Street City: Home town State: AL zip: 12345 I want to be able to export this data to either Outlook or ACT 2005, I can print to TIF (with a printer driver I purchased) and then OCR using Office 2003 Imaging or I can print to hard copy and then ...

Why is my text hidden behind other cells?
I changed my text to be 90 degrees. When I type, the text only shows in that box. I cannot make it appear OVER the other cells. I need to learn how to 'bring it to front'... if that makes sense. HELP! I don't think you can. The only ways to show all the text would be to either increase the height to the row or merge some cells together. -- Ian -- "dANA" <dANA@discussions.microsoft.com> wrote in message news:694DF522-46B4-4D66-A1F9-08469E5A7C50@microsoft.com... >I changed my text to be 90 degrees. When I type, the text only shows in >that > box....

How do I convert text to uppercase?
How do I convert all text in an existing Excel sheet that contains both upper and lower case letters? If a1 is upper then =upper(a1) will return ( UPPER) =lower will return all lower case =proper will capitalize first letter of each wor -- SilverM7 ----------------------------------------------------------------------- SilverM78's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1463 View this thread: http://www.excelforum.com/showthread.php?threadid=26264 See my web page http://www.mvps.org/dmcritchie/excel/proper.htm --- HTH, David McRitchie, Microsoft MV...

Display Data On Split Form
Not sure how to ask this but here goes. In AC 2007 I have a split form that I want to display a value that will show regardless of the row I select in the datasheet. This is the number of vacation days one will have through-out the year and of course it changes as vacations days get used up. Any help will be appreciated, James "JamesJ" <jjy@darwin_roadrunner.com> wrote in message news:6A4DFFD9-3302-4A10-B51A-153373D266FD@microsoft.com... > Not sure how to ask this but here goes. > In AC 2007 I have a split form that I want to display a value > that will show regardl...

Count Cells in a particular row w/ text
Hi, Is there a way I can total up all the cells in a range which have any text in them what so ever? Hi, =Counta(A1:A100), will count cells that are not blank if this helps please click yes, thanks "wotanthewanderer@gmail.com" wrote: > Hi, > > Is there a way I can total up all the cells in a range which have any > text in them what so ever? > Try this: =COUNTIF(A1:A10,"*") That counts TEXT only, ignores numbers. =COUNTA(A1:A10) That will count all non-empty cells regardless of data type. -- Biff Microsoft Excel MVP <wotanthewanderer@gmail.c...

Attaching a file "Inline" with text
Hi, This is a pretty simple question, but I can't seem to find the answe anywhere. I am using MS Outlook 2002. It automatically opens Word as the emai editor. Now, I want to attach a file so that it appears as an ico "inline" with the text, and NOT as an entry in the "Attachments" tex box right under "Subject". Note that I don't mean adding the contents of the attached file in th body, but I just want the file to appear as an icon in the body of th email. I know this is possible, as I receive many attachments this way, but can't seem to fin...

Can't find my Front End Folder after database split
I just split my database base to allow for ease of use for multiple users. Ererything, it seemed, went smoothly until i try to find the fron-end folder. It is nowhere to be found. Should it be label ****_fe.mbd like the back end? Please help. Thaanks. It will be called that, unless you typed something else in the save as dialog. I thought it ended up in the same folder as the backend after splitting. Easiest way is to go back to the original and split it again, this time take note of where you save it before you press the button. Jeanette Cunningham "Joe" <Joe@discussi...

Convert to Text with Heading on the first line
I have this table named tblTrans and I need to transfer this into MS-DOS Text format. I have used the DoCmd.TransferText command. My table looks like this: CODE | PAYCODE | AMOUNT | 001 001 235 001 001 111 001 001 213 001 001 212 My output should look like this 001111771 001001235 001001111 001001213 001001212 Where the first line should contain 111 as the group code (found in another table named tblGrp) and 771 is the total amount. What do you think should I do with this? I need your help. I would apprecaite w...

Unwanted replicated split screen
I know I could re-install excell but before I do, is there anyone here who can help me disable a setting that I have no idea how it came about. I can only describe it as 2 identical execl grids on the same worksheet. Accompanying image will show you. Help. Thanks. +-------------------------------------------------------------------+ |Filename: excel_display_duplication.GIF | |Download: http://www.excelforum.com/attachment.php?postid=3855 | +-------------------------------------------------------------------+ -- BoyLeroy -----------------------------------...

Split data into new sheets
I have a (very) long list, sorted by account code. I would like to write a macro that splits the list into separate sheets in the workbook, with a separate sheet for each account code. Ideally, I would also like to rename each sheet to show which account code the sheet contains. I have no idea, though, where to start. Any ideas? Thanks in advance. Hi bernard How many different accounts are in the list (more or less than 250)??? below some code that I use to split files by account numbers where the user has to select a cell within the column that contains the account number... Hope thi...

Stripping Blank Lines from text box
Hi I have a text box on a form called 'disAddress' with 'Enter Key Behavior' set to 'New Line in Field' I have 9 variables D(0) to D(8) that contains lines of addresses for exmple D(0) could equal "123 Freet Street" D(1) could equal "Near Dartford" and D(3) could equal "" and D(4) could equal "Australia" etc. I am tryng to display the address in a text box with no blank lines taking the information from the variables. D(0) = "25 Albert Road" D (1) = "" D(2) ="Wilmington" I dont want it to show ...

lookup array
Sheet 1 below: A13 is a lookup from Sheet 2. I want the cell B13 to take this value and lookup the column 1 and enter the value into B:13. =LOOKUP(A13,A2:B11) but get this #N/A? What am I doing wrong? PRESS SIZE Rate $ K68 250 K813 300 K1014 400 K1214 500 K13517 550 K13518 600 E1395/870 300 E1420/1020 400 E1650/1380 550 E1900/1450 550 K1214 #N/A Hi Order ascending the Press size column and try again hth regards from Brazil Marcelo "HFC-SC&q...

connecting text boxes that already have text inside
How do you connect two or three text boxes that are already full. I want to combined text boxes. Autoflow? Temporally, make the first box larger. Then copy and then paste into the first box. Delete the contents of box two. Select box 1, click on the chain link in the tool bar, put the cursor over box 2 and the cursor will turn into a tipped tea pot, left click. Resize box 1 and anything that can't fit in box 1 will now flow into box 2. You can link as many pages (boxes) as you wish and they don't have to be in sequence. You could link box 1 to 23 and link it to 12 and link it...