Excel:Transfer specific characters from cell to another cell.?

Question

Excel:Transfer specific characters from cell to another cell.?

Answer 1

Hi. I have much-much easier answer for you. I had exactly the same problem, and my method words perfectly, and it is very easy, unlike complicated and long formulas.



First, be sure to keep your original copy and just make a separate copy of your excel sheet to work on.

1. Select all data.

2. On the toolbar, go to Data > Text To Columns.

3. The wizard window will open. Choose type: Delimited.

4. On next screen, chose Comma and/or Space checkbox, depending on the way the commas or positioned withing your name+address line. The wizard will give you the preview right away of how your new columns will look like.

5. Click Next > Finish.

Voila!



Now, say you end up with first and last name in two different cells right next to each other.

Say, in A1 and B1, you have: Bob | Jones

To join the cells, just use this simple formula in cell C1:

=A1&" "&B1

Make sure to put one space between the quotes to add a space between first and last name in your new cell. That's all. You'll get this in one cell: Bob Jones



Now just click on this new cell and drag it all to way to the end of the table to copy the formula. This takes 2 clicks.

All of this should take you less than 2 minutes!

Answer 2

there is multiple ordinary techniques to try this. One could be to basically positioned a =LEN(cellular) formulation in to calculate the dimensions, and replica that formulation down the finished variety of your documents. Then type your documents in step with that column (LEN formulation). this might group all the lengths mutually. in case you won't be able to type the information with the aid of fact it has to stay in a particular order, then you certainly could continuously use the Conditional Formatting option to spotlight cells whose length=4 in green or purple or some coloration that makes it ordinary to become attentive to.

Answer 3

If your data is reasonably uniform then it's quite simple:



suppose data in A1 thru A5 is:

A1= Last First 1111 address St, City, ST, Zip

A2= Last First 2222 address St, City, ST, Zip

A3= Last First 3333 address St, City, ST, Zip

A4= Last First 4444 address St, City, ST, Zip



in a cell that you want Last First to display input formula as

follows but after you input =CLEAN( ... replace B with what's in B) (which is LEFT(C) ) then replace C with what's in C) and continue on until the formula is complete:

Note that D has 30 blank spaces enclosed by double quotes after the second comma ( , )

A) =CLEAN(B)

B) LEFT(C)

C) SUBSTITUTE(D),40

D) (G5," "," <30 spaces inserted here> ",2)



The formula will look like this when complete:

=CLEAN(LEFT(SUBSTITUTE(G5,""," 30 blanks",2),40))



I did it this way to explain what happened:

initially: firstname last name

first, I SUBSTITUTEd the second instance of a blank in your target cell with 30 blanks to make sure that the address was well away from the firstname lastname

we get: firstname lastname <30 spaces> 1111 addr..etc

Next, I took only the first 40 characters on the LEFT

we get: firstname lastname (with extra spaces up to 40 characters in total length for the string)

Finally, we CLEAN the result of any extra spaces

we get: firstname lastname (with NO spaces)



Read carefully and IT WILL WORK.



Hope it helps!

Answer 4

It is possible, but the chance for success will depend greatly upon whether the text is uniform in your cells. To explain I will use the example of the following text is Cell A1



Bobby Jones, 453 Back Street, Anywhere, California



To extract the name we will use the comma after "Jones" to tell the formula which text to take. So in Cell B1 type the following formula:



=SUBSTITUTE(LEFT(A1, FIND(",",A1)), ",", "")



The Substitute command is being used to erase the comma from the formula result. The Left Command is grabbing the text from the Left portion of cell A1. The Find command is telling the Left command how many characters from the left side of A1 to take.



In cell C1 type the following formula:



=LEFT(B1, FIND(" ", B1))



This formula is extracting the first name from cell B1 using the space between the names.



In cell D1 type the following formula:



=RIGHT(B1, LEN(B1)-LEN(C1))



The right command is pulling the characters from the right side of cell B1. We are using the difference in length between cells B1 and C1 to tell the forumula how many charcters to take to get the last name.



In cell E1 type the following formula:



=RIGHT(A1, LEN(A1)-(LEN(B1)+2))



This formula is taking the right characters from Cell A1 to get the address. The Len commands are telling the formula to use the difference between Cell B1 and A1 to get the number of characters to take. (the plus 2 is deleting the comma and the space from the result).



Hope this helps



P.S. If these formulas work you can apply them throughout your spreadsheet by copying and pasting. The Cell references are relative and Excel will change them for each row they are pasted into.