Formula To Split Text Into Groups Of 30 Characters But Control As Well

May 28, 2014

I have previous been given the below macro from here that splits a string of text in one cell into groups of 30 and then puts them into several cells, works perfectly.

What I would like to incorpate now is the ability to overide the point at which it splits the text.
i.e. if the inputter puts a "|" (for example) in the original text, this will cause a split in the text and it will restart its 30 count from this point onwards.

Example of what I would like to achieve:

Cell A1 = I would like to change this string of text into groups of 30, where this appears | I would like it to start a new split of 30s from this point on wards and again if another one of these | appears in the text.

Cell B1 = I WOULD LIKE TO CHANGE THIS
Cell C1 = STRING OF TEXT INTO GROUPS OF
Cell D1 = 30, WHERE THIS APPEARS

[Code]....

View 2 Replies


ADVERTISEMENT

Split Text Over X Characters

Jun 27, 2007

I have a spreadsheet that uses sql to gather data from our servers. From this data, I use concatenate function to put the data into one cell ready to copy/paste into our sms program. The program will only take 160 characters at a time..

Result I need:

For the cell to split into two/three cells if over 160 characters (and to break after the last complete word), and if appropriate, it needs to say 1of2 at the beginning of the 1st message for example, and 2of2 at the beginning of the second.

View 9 Replies View Related

Split Arabic Text Characters

Mar 22, 2008

This might be difficult. I need to split Arabic text in letters as it shown in cells.let me give an example:

ãÑÍÈÇ-Èßã-åäÇ-Ýí-ÇæÒßÑíÏ

Data>text to columns convert only to original alone letters:

ã ÑÍ È Ç- È ß ã - å ä Ç- Ý í- Ç æ Ò ß Ñ í Ï

But what I need is split the letters as it mixed in the same cell:

ãÜ Ñ ÍÜ ÈÜ Ç- ÈÜ ßÜ ã- åÜ äÜ Ç- ÝÜ í- Ç æ Ò ßÜ Ñ íÜ

View 5 Replies View Related

Formula To Split Characters In Cell

Jan 14, 2009

In A1 cell i have the following text
"CAMPBELL # ERASCO O ZMKE # UNGARISCHE GULASCHSUPPE # 390 ML"

A2: CAMPBELL
A3: ERASCO O ZMKE
A4: UNGARISCHE GULASCHSUPPE
A5:390 ML

View 9 Replies View Related

Excel Formula To Split Words With Characters Limit Per Cell?

Dec 6, 2010

I am suffering with split a long descriptions into 3 cells with criteria 1st cell not more than 30 characters, 2nd cell not more than characters and 3rd cell will locate the remaining characters there. I think this is quite easy if I use LEN/MID/RIGHT/LEFT formula. However, I wish the formula will smart enough to split word by word. refer to example below:-

"My lecturer replied, that i really did very bad in final, nothing's gonna change my plan."

If I use left(A1,30) formula, the result is "My lecturer replied, that i re"however, the word "really" is cut half way. I am finding the formula that split description to not more than 30 characters and won't cut my string and become incomplete word. Expected result should be 1st cell "My lecturer replied, that i", then "really did very bad in final," at 2nd cell.

View 5 Replies View Related

Split Percentages Into Groups Of 10 For 5 Segments

Jul 11, 2014

I have a percentage based input whose values can change, example:

Input matrix.png

I would then like this translated into a matrix that can adjust depending on the segment % input; like so:

Matrix.png

View 9 Replies View Related

Identifying Groups Of Control Names

Nov 4, 2006

I have successfully generated my code to validate the 60 + text boxes in a MultiPage UserForm. Now I need to identify those TextBoxes that require validation. To aid me I have distinguished those which need to be validated by naming them with a unique suffix, (i.e., _Text vs. _Txt). This code will participate as a Boolean within a With statement as part of the identifying validating code. I realize that a
Right(?, 5) = “_Text”

statement should do the trick but I can’t seem to correctly get the appropriate syntax to use within the parenthesis. Any suggestions? Is there a better methodology out there to accomplish this?

View 9 Replies View Related

Split & Copy Groups/Segments Based On Cell Value In Column

May 6, 2008

This is my first post but I have been using Ozgrid for awhile now. I am farily good with excel formulas but have just started with macros so bear with me if i dont understand what you mean at first.

I am looking for a way to copy rows our of sheet2 in the attached sheet based on the value in the segments column in sheet 2. The rows need to be paste into sheet3 (already has heading set up). The segments value is the number of times i need each row copied into the next sheet. The purpose of this is to split random length samples into 10cm incriments for study. For example, a 1.5m sample is taken so there should be 15 segments of 10cm each copied into sheet3.

Also, if possible, it would be nice for it to display the actual length of the segment after copied into sheet3 for cases where the length was not evenly divisible by 10. I have found several examples of row copying macros, but none that will copy a conditional number of hte same row based on a cell value. In the original data there are close to 4000 rows, but the number of rows will vary depending on the data source.

Another thought I had was if the total number of available rows is going to be exceded would it be possible to have the rows pasted into different sheets based on the rock type listed in the column?

View 9 Replies View Related

Text Formula? Postcodes: Seperate The First Or First Two Text Characters From The Rest Of The Postcode

Feb 16, 2007

I am working on sales information which includes postcodes. What i need to do is seperate the first or first two text characters from the rest of the postcode. I have attached a small snipet of what i am working on. Currently i am using the =Left(A4,2) but this will give me in some case a numerical value aswell. For example E1 or G1 in the case of the sample attached. Is there a formula that exists where it will just return the text values in a cell and not numerical values.

View 6 Replies View Related

Split Text Across Columns Formula

Dec 9, 2006

I got a question regarding a what formula can i use to act like the Split method in programming. I would like if this can be done with a formula not programming cos i am a programmer Basically i got a column full of data in the following format:.dddd.ddddddd.ddddd. Now the number of the "d" can be random in between the dots. So i would like to be able to split the line by looking at the "." .There is one thing though that each line does have 3 or 4 "." characters.

View 3 Replies View Related

Formula: Split A Text String Based On A Symbol

Oct 13, 2009

is there a function that will split a text string based on a symbol. I know how tyo use left and right, which are based on a set number of characters but I want to split based on a "/" mark. whats to the left of the "/" mark and whats to the right of the "/" mark. any ideas. an exaplme is: tom / tim. i want a formula that will put the word "tom" in a cell and another formula that will put "tim" in another cell.

View 9 Replies View Related

How To Split Up Group Of Characters

May 27, 2014

Need to split a group of up to 15 characters to be in a specific cells.

An example of what would need to be split up would be the following:

ASJ-Z61-123456-714

Need it to be in the following Format:

Letters | Number | Number | Number | Number | Numbers | Number| Number
Only or letter or letter
symbol | company | market | ledger | sub region | serial/Account Number | Number | Year

AS | J | Z | 6 | 1 | 123456 | 71 | 4

I am just not sure what function or formula that I need to use.

View 4 Replies View Related

Formula: Last X Characters, Text Or Numeric

Dec 7, 2006

I am trying to write a formula that will recgonise either text or numicial value as the result is used with a match formula. In column C I have data as follows:

1400 SBY
1230 9985
ADO

I am using a =--RIGHT(C4,4) formula in column E to get the required data and then using my match formula to extract other data. How can I rewrite the above formula so that it can read either text or numbers that will allow my match formula to work.

View 5 Replies View Related

Split The Characters And Display Other Cells

Nov 4, 2009

I want to accept a string, well typically a name say "Manik" from one of the cells in Excel say "B2"; then split it as characters M a n i k and display it in five other cells like say from D2:D6. how can I do it? Also the name may change its not a fixed string.

View 9 Replies View Related

Split Cell Contents To New Rows 50 Characters In Each Row

Jun 6, 2014

I have data in Col A. and i want to split (50 characters) in each row.

I know the formula but dragging formula to following rows is not working.

I have to write the formula manually. I write in b1 B2 and B3 as follows.

I want following

=MID(A1,1,50)
=MID(B1,251,50)
=MID(C1,501,50)

When i drag down the formula to bottom rows it repeat

Desire Formula in COL B

aaa asdf 242424 alkjsaf 32234 asfasf 23234 dfdfdfd
=MID(A1,1,50)

aaa asdf 242424 alkjsaf 32234 asfasf 23234 dfdfdfd
=MID(B2,251,50)

[code]....

I want to copy down the formula to 10k rows. When i select b2 and b3 and drag down i get following results.

aaa asdf 242424 alkjsaf 32234 asfasf 23234 dfdfdfd
=MID(A1,1,50)

aaa asdf 242424 alkjsaf 32234 asfasf 23234 dfdfdfd
=MID(B2,251,50)

aaa asdf 242424 alkjsaf 32234 asfasf 23234 dfdfdfd

[code]....

View 4 Replies View Related

Macro To Split Rows Into Groups Of 5 And Insert 3 Blank Rows In Between

Feb 9, 2013

I would like to have my macro code search column A (supplier numbers) and split the rows into groups of rows of 5 or less and then insert 3 blank rows between each group of rows. The split needs to start on a new supplier number and cannot split a supplier number into two different groups. Here is a sample:

Supplier
Invoice Date
GL Date
Invoice Amt

[Code].....

View 1 Replies View Related

Trim Special Characters In Cell To Be To Split Data Into Columns?

Apr 9, 2014

This formula I want to apply it in another workbook. It split in different columns the content of a cell.

The formula is below:

[Code] .....

In cell A2 I have the following data:
|516582-001-99|414816-001-99|414816-003-99|516582-001-99|

If I apply the formula above in cells B2 to E2 it returns a blank cells. But if I delete the first "|" sign in the left side manually the formula works perfectly by splitting the cell into columns from B2 to E2. The issue here is that I have more than 300,000 records. Just imagine the amount of time invested in just deleting the first "|" at the left side.

I need a variation of the formula above that in first place delete the first "|" at the left side and after that continue with the proper work of the formula.

View 5 Replies View Related

Formula - Age Groups

Mar 19, 2007

i am setting up a work sheet on ages for a sporting team

i want to be able to type in the date of birth and have it automatically come up with there age then for it to go to an age group

eg

14-3-00 would go to 7 yrs then go to under 8's

View 11 Replies View Related

How To Split Text From Text String Into Separate Columns - No Delimiters

Apr 8, 2014

I have the cell data as below

How would I split into a new column the first part which is a date into a new column, then the country and the remainder into separate columns?

I still want the original data as I need to check that the splits worked well?

16.5.90 CH 1671/90-4
18.10.1991 CH 3056/91-1
24.07.92 ch 2341/92-2
30.7.92 ch 2395/92-3
18.11.92 Us 3533/92-5
26.5.93PCT 1577/93-0
9.8.93 CH 2363/93-8
17.8.93 CH 2445/93-0
25.1.94ch209/94-6;8.12.94ch3714/94-1
25.1.94 ch 209/94-6 ; 8.12.94 ch 3714/94-1
8.4.94 ch 1047/94-0
22.4.94 ch 1255/94-7
18.11.1992 CH 3533/92-5
18.11.1992CH 3533/92-5

View 2 Replies View Related

Formula To SUM Different Groups In Same Column?

Aug 22, 2014

Attached workbook is self explanatory but Cols B-C have matching data in groups with a value for each row in Col D.

I need a formula to SUM each group and input that SUM value in the adjacent Col/Row E

View 6 Replies View Related

Formula To Find Age Groups

Nov 8, 2008

I have a column with customers ages in it. Now what I'd like to do is create a formula that checks all the age cells, then gives a percentage for each age group of my choosing. I tried some IF formulas but I just can't figure it out

View 4 Replies View Related

Split Text File Into Smaller Text Files

Mar 15, 2005

I'm trying to take a large text file and break into smaller text size files. I want to open the data file, and parse into 5000 line smaller files.

Here is what I have based off of some MS KB
Sub LargeFileImport()

'Dimension Variables
Dim ResultStr As String
Dim FileName As String
Dim FileNum As Integer
Dim Counter As Double
Dim CounterMax As Double
Dim sPath As String
Dim FileCounter As Double
'Output File

View 9 Replies View Related

Deleting Groups Of Rows When Specific Text Data Is Populating Row?

Feb 14, 2013

I have a complex report with thousands of lines I am trying to clean up. In order to do this, I need VBA that recognizes specific text data starting after row 9. When it sees this, it will need to select and delete the 2 rows above it, the row it is in, and the 6 rows below it. 9 rows in total, some of which are blank. The text will repeat every 47-50 rows, but I cannot say exactly when it will repeat again. When it is repeated, I need the 9 rows deleted. At this time, there is only 1 column in the report because I need to split out the data in the cell into adjoining cells. Since I have not solved that yet, I need to just delete the entire row.

The text is written with spaces between each letter and a double space between words. It reads, "B I L L I N G M A S T E R I N V O I C E S E T U P L I S T I N G"

View 8 Replies View Related

Excel 2010 :: Calculate Most Common Text Values By Groups

Mar 14, 2013

I want to calculate the most common text value for groups. I have a table with a lot of records like an example below.

ID Code
1 HB
1 KU
1 KS
1 LV
1 KS
2 LM
2 KS

[Code]....

I want a table like below:

ID Code
1 KU
2 KS
3 KS

How can I do it in Excel 2010?

View 9 Replies View Related

Text To Columns :: Macro To Split Text

Jun 17, 2008

I am trying to write a micro code to split text which is copied into cell A1 into columns. I can do this fine by going to "data" the "text to Columns" and selecting the places i want to split the text (this is the same for every piece of data i copy in).

The macro works perfectly every time. the problem is that the spreadsheet is shared and i want to protect certain cells on the sheet, when i protect the sheet the recorded macro does not work as the "data", "text to columns" is not available in a protected workbook.

I was just wondering if someone could help me, so i can run a macro to split the text which also allows me to protect cells. In the "text to column" option the "fixed width" (column breaks) i choose are: 4, 25, 34 and 43.

View 11 Replies View Related

Validate Cell For Text Length & Characters In Text

Mar 13, 2008

I have a cell (B2) I would like to apply multiple data validations to.

I know I need to use the custom formula option but don't know how to write the formula.

I don't even know if it is possible, but here is what I'm after

I need to make sure the cell is 4 digits long

I need to make sure the cell starts with a zero (Because the cell starts with a zero I have it as a text cell)

I need to make sure the 2nd number is not 0 if A2 begins with 5 (A2 is also a text cell).

View 6 Replies View Related

Split The Text..

May 10, 2009

I have in one column first name space and then surname. I want to divide it into two columns 1 for the first name and 2 for the surname.

View 2 Replies View Related

Split Text Into Columns?

Jun 4, 2014

I am having quite a bit of a challenge here and am not able to code to split the text into columns. The text to columns does not work here unfortunately. Below is my situation. In one column that has the contract details I have the data as follows:

Account Manager Jennifer MacFarlane CONSULTING - GENERAL on 20-JUN-13 Function #:176749
Account Manager Janet Bewers CONSULTING - GENERAL on 25-JUL-13 Function #:176878
Account Manager Janet Bewers HEAT STRESS AWARENESS on 27-JUN-13 Function #:176828
Account Manager Janet Bewers TRACTOR SAFETY AWARENESS on 08-AUG-13 Function #:177383

What do I key in to get Account Manager in one column, the name of the person in another column and the one in caps in another column and the date in one column and the function in another column. I tried using left, right and LEN and something is terribly wrong with my logic

View 4 Replies View Related

Split Text In A Column

Mar 24, 2009

I currently working on an excel report that contains a sheet with the following data in one column.

GARLAND CRAIG mr 850
GARLAND CRAIG ms 850
HARTSHORNE JANE mrs 965
ROBINSON RACHEL master 3687

I would like to split the names from the title and the id number and place them in three different columns one for name,the other for title and the third for id number.Is there a formula that could do this.

View 5 Replies View Related

Split Text And Number VBA?

Sep 30, 2012

I have some text like this in a cell

First Name Surname Number,First Name Surname Number,First Name Surname Number etc. e.g John Smith 20, Billy Sharp 40, J Rodriguez 50

I need to split the text so that it looks like this

John Smith
20

Billy Sharp
40

J Rodriguez
50

I have tried to have a go but have come stuck

Code:
Sub Split_Name()
Dim str As String
Dim a
str = Sheets("Sheet1").Range("A1").Value
a = Split(str, ",")
For i = LBound(a) To UBound(a)
???????????????????????
Next i
End Sub

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved