Move Selected Non-consecutive Text Characters From One Cell To Another
Feb 27, 2014
I am trying to select and move text from one column to another. I have roughly 1,000 cells in column A and I would like to tell Excel to select characters at certain positions and move them into the adjacent column.
I want it to look like this...
column A column B
ABCDEFGHI BCI
Each cell in column A has a 9-letter sequence. I would like to move characters 2,3 and 9 into the adjacent column
View 2 Replies
ADVERTISEMENT
Feb 27, 2013
I have two colums of data; A = buyer name(s); B = seller name(s); there are 30,000 rows.
I'm looking for a function than can tell me if any 4 consecutive charactetrs match between A1 and B1, A2 and B2, A3...
eg. If cell A1 = John James, Holly Judd. B2 = Emily James. I would like the function to return True in colums C as James appears in both cells (the aim of this is to identify trades between family memebers)
I have searched forums and know of MATCH and EXACT but don't know how to adapt for this example.
View 2 Replies
View Related
Dec 23, 2007
I am using Excel to create a parts list for a manufacturing assembly. I intend to have 6 categories (columns) for each part or instance. My problem is that the last (furthest right) entry will sometimes contain more characters than others. I want to limit the number of characters in the cells of this last column, and I have seen how to bump the remaining characters down to the next row(s) in the same column using the LEFT and MID formulas. This actually works very well for that purpose. However, I need to know how to force the next instance (row of 6 cell) to start together on the next available row.
The following images should better illustrate my question. First, I show the source data with the contents of cell F2 without further formatting. This cell contains more characters than I want making the column too wide for the printable space allowed. http://www.ozgrid.com/forum/attachme...1&d=1198374241
The next image shows the desired result with the extra characters from cell F2 moved to F3. (This data could actually fill up to 4 or 5 cells vertically.) Here, I have manually entered cells A4 through F5 for this illustration. I need to know how to automatically have A, B, C, D, & E move along with F. http://www.ozgrid.com/forum/attachme...1&d=1198374241
View 7 Replies
View Related
Jul 31, 2012
I have 2 col A & F. F col associate with date values & Col A has some color cells.I want to move down those color cells to a selected non-color cell row when run a macro.
I have code which perform to move color cells down to selected cell but problem is, if i have A4 & A6 color cells and i selected F2 cell & run macro then color cell A4 & A6 move down to cell A2 but cell A3 move to place A6. The sequence is
A1A2A4A6A5A3A7A8 & so on but i want cell A3 just push down the order & sequence will be
A1A2A4A6A3A5A7A8 & so on My code is as
Code:
Public Sub Move_colored_cells_rows()
' Instead of moving the entire row , 10 columns will be moved
Const NUMBER_OF_COLUMNS = 10
[Code]....
View 2 Replies
View Related
Sep 6, 2013
I have a macro to sort a very large list of materials by a variety of conditions.
The macro runs automatically when a cell on the master list spread sheet is selected and using If/ElseIf it sorts based on what cell they have selected.
Example
If ActiveCell = "ALUMINIZED" Then
ActiveSheet.Range("$A$14:$K$1945").AutoFilter Field:=2
ActiveSheet.Range("$A$14:$K$1945").AutoFilter Field:=4
ActiveSheet.Range("$A$14:$K$1945").AutoFilter Field:=1, Criteria1:= _"Aluminized"
Which is working perfectly. The problem I am having is that some of the cells are two lines so it looks like this:
BE
BEARINGS
I don't know how to get it to launch based on that cell content because of the line break in there. Can I make it launch based on the first two characters only?
View 5 Replies
View Related
Apr 18, 2013
When I use the mouse pointer to select a cell I can't use the arrow keys to move to another cell while the pointer is over the cell and I can't edit the cell while the pointer is over the cell. If I move the pointer away from the cell then I can move around and edit as normal therefore I don't think this is a scroll lock issue.
This issue also happens when I select a tab. If I select a tab and then leave the pointer over the tab I selected then I can't use the arrow keys to move around the worksheet or edit a cell; if I move the pointer away from the cell then I can move around and edit as normal.
I am using MS Excel 2010.
View 1 Replies
View Related
Aug 22, 2009
I have an array, let’s say B1:D20. I would like to be able to manually select any cell within this array and then run a macro which will place the contents of the selected cell into the cell in column A in the same row as my selection.
View 3 Replies
View Related
Jan 27, 2012
A button macro that will move the whole row of the selected cell to another sheet?
View 3 Replies
View Related
Jul 4, 2007
How do I after entering data into say cell A1 and jumping it to say cell F3 and enter data and then another and so on
View 2 Replies
View Related
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
Aug 14, 2008
I pasted in 1369 characters (including spaces) to a cell, and NO MATTER what I try, all characters will not print.
If I have the cell up for formatting on the function line, all text can be seen, but for some reason it cuts off the last sentance or more and will not show it in print preview.
I've tried all kinds of cell text formatting, cell merging, etc. with no luck. The only work around I found is to just have the "missing text" on the following row.
View 4 Replies
View Related
May 6, 2009
This is very similar to my previous post, which was solved. Now that I've extract the numbers, I need to extract the text for the specific work activities, for example 13Z or 9GGG. I'm assuming some variation on this formula:
=LOOKUP(9.99E+307,--MID(C4,MIN(FIND({1,2,3,4,5,6,7,8,9,0},C4&1234567890)),ROW(INDIRECT("1:"&LEN(C4)))))
is the solution, but I'm struggling with making the correct alterations.
View 2 Replies
View Related
Aug 18, 2014
Is there a function that can remove all text and other characters from cell and only keep the numbers? The numbers can be randomly in the cell so not only in the end or beginning.
See attached file.
View 7 Replies
View Related
Aug 8, 2007
This should be simple, but I am struggling with finding a way to use the search or find function to identify text characters. This is my situation, I have for example a cell that contains FW023 or D1234. I need to be able to count the number of characters that are text.
i would think I would be able to do it with the search or find function, but can't figure out how to get it to just count the number of text characters with in it.
View 9 Replies
View Related
Nov 18, 2008
a formula that will count the txt chars in a cell example ie "aa99" result would be 2 or "aa99aa" equals 4
View 9 Replies
View Related
Mar 27, 2008
I have some cells in a spreadsheet that contain unique numbers as the beginning of the cell (these numbers are always ten characters). In the rest of the cell there is text. the data would look something like this:-
SGGHNVT561 - 3yr Maintenance
I also have some cells which dont have the unique number at the begging, so the data would just look like:-
3yr Maintenance
In another table I have a list of the unique numbers and the name that they correspond to, for example (this is the 2 columns)
SGGHNVT561 - Dave
HUKIDO8946 - Stuart
HJUTIFHE78 - Graham
I have ben trying to construct a formula that only searches the beginning of the cells to see if it containts one of the unique numbers and if it does to put the name of the person beside the cell. If the unique number is not there to return a value like 'Check'.
i tried vlookup but then i ran out of ideas :/
this is what i came up with so far:-
=IF(P7="","",VLOOKUP(P7,'[Master List.xls]Sheet1'!$B$2:$C$5,2))
The only problem i can see that this is looking for the full cell to be within the master list - but its really just the first 10 characters I want it to check
View 5 Replies
View Related
Sep 15, 2006
I'm working on a spreadsheet that has a title sheet with a list of company names, each subsequent sheet has that company’s info. I have a macro in it that uses the name in A1 (on the company info sheets) to name the tab (with error checking).
What I now need (and can't for the life of me figure out how to do) is to have a link between the title sheet -when you click on a company name- and the company info sheet.
The company info sheet had the same exact name in A1 and on the tab as what is on the title sheet.
View 9 Replies
View Related
Feb 27, 2014
In an active cell I have this value : " Hello.xlsm"
I need a MACRO code that will take out the characters ".xlsm".
I am trying to focus on the active cell and not a range.
View 3 Replies
View Related
May 28, 2014
I have a cell which contains the following information:
01E4R3; 01W5; 01M4G3; 01W5
I want to sum up just the numbers in that cell. In this example, the answer should be: 30
View 9 Replies
View Related
Sep 2, 2008
Is there a way to specify if there are 46 characters in a cell, to apply text wrap, indent the second line, and resize the row height to 25.5?
For example, I always will have text on merged cells B7:C7. I'd like to have a macro that determines if the text goes over C7 (I figured that it would take 46 characters to do this), that the merged cells will be text wrapped, then row 7 will be resized to 25.5.
I know how to record a macro that will text wrap and resize the row height but am not sure how to do the "if" condition.
View 9 Replies
View Related
Feb 1, 2008
I wish to limit the number of text characters in a cell and have excel prevent the entry of additional characters after limit is reached. I have tried the Data Validation but it does not preven the entry of additional characters. I want to be able to show the error immediately when the limit is reached and no additional characters are permitted.
View 3 Replies
View Related
Oct 25, 2007
I have a workbook with multiple sheets. I need to create a macro and associate it with a command button that will cut the row the user has selected and paste it into the last empty row on another sheet in the same workbook, then, go back to the first sheet and delete the empty row.
View 3 Replies
View Related
Jul 25, 2014
I am using this code to hide or unhide rows of text on another sheet:
VB:
Sub ProcessSheet1ChangeOnCellJ7(ByVal Target As Range)
Dim sAddress As String
Dim sValue As String
'Get the address of the cell that changed without '$' signs
sAddress = Target.Address(False, False)
[Code]....
When the "Not Pursuing" list box option is selected (in cell "J7" or "J8" in Sheet 1) I need to add (or over-write) "Not Pursuing" to the range of cells in column "B" (in the "Tasks" sheet), but only for that particular Goal, meaning a limited range of cells in column "B". If the "Pursuing - Show All Tasks" option is selected for a Goal then these same cells need to be blank so that the appropriate person can enter their name into the cell.
The purpose for adding "Not Pursuing" automatically to these yellow highlighted cells is that it will facilitate filtering of tasks by individual in the "Tasks" sheet..
Again I have tried several times to upload a sample file and am unable to, which I know makes it more difficult to solve. (Is there some common mistake people make? I know it's an allowed format and is very small in file size....)
Code solution can be entered directly beneath:
VB:
If Target.Value = "Not Pursuing" Then
ActiveWorkbook.Sheets("Tasks").Rows("29:29").EntireRow.Hidden = False
ActiveWorkbook.Sheets("Tasks").Rows("30:48").EntireRow.Hidden = True
View 1 Replies
View Related
Apr 25, 2009
There are basically four fields of text (date, trans. #, merchant, $ amount) all in one cell that I would like to have in 4 separate columns without having to cut-paste, cut-paste, cut-paste. Is it possible for me to break this information up quickly into 4 separtate columns? I have about 400 lines of text like this and would like to find a faster way to do it.
View 10 Replies
View Related
Oct 15, 2012
I'm faced with receiving data for time, in a text format, as follows:
Example of possible cell contents...
20secs
40m20secs
2h40m20secs
I'm not interested in the secs value but need to isolate the h (hours) and m (mins) values to use in another formula as numbers. The time could be shown in either of the above formats with either; (a) just secs, (b) mins & secs or (c) hrs, mins & secs... and of course the hours or mins values could be 1 or 2 digits in length. How can I determine (using formula only, not vba) what the values for hours and/or mins are dependent upon what is present?
View 9 Replies
View Related
Apr 14, 2009
I'm am trying to restrict the length of data that can be entered into a cell based on a drop down in that row. I have achieved this, but what I need to be able to do is use data validation to restrict the cell length to one of two values - so for example the length can only be 6 OR 9 characters long.
I cant see how to do this. At present the best I can do is rectrict to between 6 and 9 - but this would allow 7 and 8 which is not allowed.
View 3 Replies
View Related
Oct 16, 2006
I'm trying to automate a very laborious process where text data is taken from a series of cells in excel 97 and is turned into HTML tables - I've got it all working except for one thing, when I extract the contents of the text the paragraphs are lost and are merged into one long piece of text - obviously I need to insert <br /> characters where the paragraphs are to get this working. how I can extract text from with some kind of marker for the paragraph sections? Or is there a way to insert characters where the excel cell has a paragraph mark (ie a return key has been pressed).
View 2 Replies
View Related
Jun 20, 2014
I am trying to do is to make it so that users can enter data into my form (I have managed to create the form and am working on trying to figure out how to get the info entered into the form to appear in my worksheet) and then the data will cause the information to, basically, compile sentences that I want to appear in one of three cells at the bottom of the worksheet.
Essentially, at this point in time, the end goal is to make 3 cells that contain text. Eventually, this text will be indexed elsewhere, but that is for the next person to deal with. I am creating a prototype that I want to present as a process improvement.
So, when the user selects 3 in cell D7 I want the text "Customer purchased 3 Widgets at $20 each." to print in cell 82B.
I also want the user to be able to copy and paste some customer records from another program directly into a cell in the form that will just print (unchanged) into one of these boxes, too.
Lastly, I want the user to be able to select checkboxes that will, when selected, print a comment that will print in two of these boxes. For example, if the user selects yes for cell D26 then I want the text "Customer not eligible for free shipping" to appear in both cells C82 and D82.
View 5 Replies
View Related
Feb 25, 2008
Once the user has submitted the data and the results are shown on Sheet1, how can i move that data into selected fields on sheet4.
find the attached excel sheet...
View 11 Replies
View Related
Apr 19, 2012
A cell is having some content eg. "This is MrExcel Forum". Now the user selects (By dragging the cursor or Shift + Arrow keys) - "is MrE".
Is there a way i can access only this selected data thru vba?
Something like when u select some text in a cell & right click > Look-up , the selected text shows up on the look-up box.
View 3 Replies
View Related
