Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
edit text string in column B
How to edit column B in spreadsheet so that the 1st 14 characters in the
string is cut/pasted to column Y, and the last 14 characters in the string is cut/pasted to column Z? Need to apply such an edit to all cells in column B. Spreadsheet is A1:Pxxxx (approx 3000 rows). All cells in column B contain a character string that represents 'model number - description label - part number'. String varies in length due to description label differences, however model number and part number are always 14 characters each. Cell format is general. MS Office Excel 2003, SP1 MS Windows XP Professional 5.1, SP 2, build 2600 Thanks for your help, R |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
edit text string in column B
In cell Y1 enter the formula:
=left(b1,14) In cell Z1 enter the formula: =right(b1,14) Copy the formulas down as needed. HTH, Elkar "useR" wrote: How to edit column B in spreadsheet so that the 1st 14 characters in the string is cut/pasted to column Y, and the last 14 characters in the string is cut/pasted to column Z? Need to apply such an edit to all cells in column B. Spreadsheet is A1:Pxxxx (approx 3000 rows). All cells in column B contain a character string that represents 'model number - description label - part number'. String varies in length due to description label differences, however model number and part number are always 14 characters each. Cell format is general. MS Office Excel 2003, SP1 MS Windows XP Professional 5.1, SP 2, build 2600 Thanks for your help, R |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
edit text string in column B
That works fine for copy/paste, but what I need is a cut/paste result. That
is, the 1st 14 char and the last 14 char cannot remain in column B. Thanks, R "Elkar" wrote: In cell Y1 enter the formula: =left(b1,14) In cell Z1 enter the formula: =right(b1,14) Copy the formulas down as needed. HTH, Elkar "useR" wrote: How to edit column B in spreadsheet so that the 1st 14 characters in the string is cut/pasted to column Y, and the last 14 characters in the string is cut/pasted to column Z? Need to apply such an edit to all cells in column B. Spreadsheet is A1:Pxxxx (approx 3000 rows). All cells in column B contain a character string that represents 'model number - description label - part number'. String varies in length due to description label differences, however model number and part number are always 14 characters each. Cell format is general. MS Office Excel 2003, SP1 MS Windows XP Professional 5.1, SP 2, build 2600 Thanks for your help, R |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
edit text string in column B
Ok, use the formulas just like I suggested, then add the following steps:
Copy columns Y and Z While still selecting Y and Z, "Paste Special" Select "Values" from the dialog box Click OK Y and Z now no longer contain formulas and you can delete column B if needed. HTH, Elkar "useR" wrote: That works fine for copy/paste, but what I need is a cut/paste result. That is, the 1st 14 char and the last 14 char cannot remain in column B. Thanks, R "Elkar" wrote: In cell Y1 enter the formula: =left(b1,14) In cell Z1 enter the formula: =right(b1,14) Copy the formulas down as needed. HTH, Elkar "useR" wrote: How to edit column B in spreadsheet so that the 1st 14 characters in the string is cut/pasted to column Y, and the last 14 characters in the string is cut/pasted to column Z? Need to apply such an edit to all cells in column B. Spreadsheet is A1:Pxxxx (approx 3000 rows). All cells in column B contain a character string that represents 'model number - description label - part number'. String varies in length due to description label differences, however model number and part number are always 14 characters each. Cell format is general. MS Office Excel 2003, SP1 MS Windows XP Professional 5.1, SP 2, build 2600 Thanks for your help, R |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
edit text string in column B
No, that won't do it either.
Problem is that there are 3 segments to the char string in column B (model number - description label - part number), as decribed below. My goal is to separate the 3 segments into 3 columns. Prefer to leave the description label segment in column B, and "move" model number and part number segments to Y and Z respectively. Sorry if I was not clear earlier in the discussion. Thanks, R "Elkar" wrote: Ok, use the formulas just like I suggested, then add the following steps: Copy columns Y and Z While still selecting Y and Z, "Paste Special" Select "Values" from the dialog box Click OK Y and Z now no longer contain formulas and you can delete column B if needed. HTH, Elkar "useR" wrote: That works fine for copy/paste, but what I need is a cut/paste result. That is, the 1st 14 char and the last 14 char cannot remain in column B. Thanks, R "Elkar" wrote: In cell Y1 enter the formula: =left(b1,14) In cell Z1 enter the formula: =right(b1,14) Copy the formulas down as needed. HTH, Elkar "useR" wrote: How to edit column B in spreadsheet so that the 1st 14 characters in the string is cut/pasted to column Y, and the last 14 characters in the string is cut/pasted to column Z? Need to apply such an edit to all cells in column B. Spreadsheet is A1:Pxxxx (approx 3000 rows). All cells in column B contain a character string that represents 'model number - description label - part number'. String varies in length due to description label differences, however model number and part number are always 14 characters each. Cell format is general. MS Office Excel 2003, SP1 MS Windows XP Professional 5.1, SP 2, build 2600 Thanks for your help, R |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
edit text string in column B
No problem, this can be done. Keep the previous steps to get data into
columns Y and Z. Now, to get B to display just the description. Insert a helper column next to column B. In this new column, enter the formula: =MID(B1,15,LEN(B1)-28) You many need to adjust the numbers slightly if your data contains dashes or spaces that you want to remove. Now, copy your helper column onto Column B using "Paste Special" and "Values". Then delete the helper column. Now, hopefully, you should be where you want to be. Elkar "useR" wrote: No, that won't do it either. Problem is that there are 3 segments to the char string in column B (model number - description label - part number), as decribed below. My goal is to separate the 3 segments into 3 columns. Prefer to leave the description label segment in column B, and "move" model number and part number segments to Y and Z respectively. Sorry if I was not clear earlier in the discussion. Thanks, R "Elkar" wrote: Ok, use the formulas just like I suggested, then add the following steps: Copy columns Y and Z While still selecting Y and Z, "Paste Special" Select "Values" from the dialog box Click OK Y and Z now no longer contain formulas and you can delete column B if needed. HTH, Elkar "useR" wrote: That works fine for copy/paste, but what I need is a cut/paste result. That is, the 1st 14 char and the last 14 char cannot remain in column B. Thanks, R "Elkar" wrote: In cell Y1 enter the formula: =left(b1,14) In cell Z1 enter the formula: =right(b1,14) Copy the formulas down as needed. HTH, Elkar "useR" wrote: How to edit column B in spreadsheet so that the 1st 14 characters in the string is cut/pasted to column Y, and the last 14 characters in the string is cut/pasted to column Z? Need to apply such an edit to all cells in column B. Spreadsheet is A1:Pxxxx (approx 3000 rows). All cells in column B contain a character string that represents 'model number - description label - part number'. String varies in length due to description label differences, however model number and part number are always 14 characters each. Cell format is general. MS Office Excel 2003, SP1 MS Windows XP Professional 5.1, SP 2, build 2600 Thanks for your help, R |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
edit text string in column B
Use Data-Text to columns. You can use fixed width to seperate the first 14
chars. This will seperate the column into two columns. Do it again on the second column selecting delimited and select the charector that seperates the description from the part number. Then you should have three columns that you can cut and paste where you want them. "useR" wrote: How to edit column B in spreadsheet so that the 1st 14 characters in the string is cut/pasted to column Y, and the last 14 characters in the string is cut/pasted to column Z? Need to apply such an edit to all cells in column B. Spreadsheet is A1:Pxxxx (approx 3000 rows). All cells in column B contain a character string that represents 'model number - description label - part number'. String varies in length due to description label differences, however model number and part number are always 14 characters each. Cell format is general. MS Office Excel 2003, SP1 MS Windows XP Professional 5.1, SP 2, build 2600 Thanks for your help, R |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
edit text string in column B
Thanks, this works great!
R "Elkar" wrote: No problem, this can be done. Keep the previous steps to get data into columns Y and Z. Now, to get B to display just the description. Insert a helper column next to column B. In this new column, enter the formula: =MID(B1,15,LEN(B1)-28) You many need to adjust the numbers slightly if your data contains dashes or spaces that you want to remove. Now, copy your helper column onto Column B using "Paste Special" and "Values". Then delete the helper column. Now, hopefully, you should be where you want to be. Elkar "useR" wrote: No, that won't do it either. Problem is that there are 3 segments to the char string in column B (model number - description label - part number), as decribed below. My goal is to separate the 3 segments into 3 columns. Prefer to leave the description label segment in column B, and "move" model number and part number segments to Y and Z respectively. Sorry if I was not clear earlier in the discussion. Thanks, R "Elkar" wrote: Ok, use the formulas just like I suggested, then add the following steps: Copy columns Y and Z While still selecting Y and Z, "Paste Special" Select "Values" from the dialog box Click OK Y and Z now no longer contain formulas and you can delete column B if needed. HTH, Elkar "useR" wrote: That works fine for copy/paste, but what I need is a cut/paste result. That is, the 1st 14 char and the last 14 char cannot remain in column B. Thanks, R "Elkar" wrote: In cell Y1 enter the formula: =left(b1,14) In cell Z1 enter the formula: =right(b1,14) Copy the formulas down as needed. HTH, Elkar "useR" wrote: How to edit column B in spreadsheet so that the 1st 14 characters in the string is cut/pasted to column Y, and the last 14 characters in the string is cut/pasted to column Z? Need to apply such an edit to all cells in column B. Spreadsheet is A1:Pxxxx (approx 3000 rows). All cells in column B contain a character string that represents 'model number - description label - part number'. String varies in length due to description label differences, however model number and part number are always 14 characters each. Cell format is general. MS Office Excel 2003, SP1 MS Windows XP Professional 5.1, SP 2, build 2600 Thanks for your help, R |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Wrap text in column headers to fit text in column | Excel Discussion (Misc queries) | |||
Count Position of Filtered TEXT cells in a column | Excel Worksheet Functions | |||
Count Position of Filtered TEXT cells in a column | Excel Worksheet Functions | |||
another text to column problem | Excel Worksheet Functions | |||
Counting every unique text string in a column | Excel Worksheet Functions |