Insert Specific text into fields

  • Thread starter Thread starter bholabhala
  • Start date Start date
B

bholabhala

Here is my problem. I have 10,000 text line that look like this
AAAAA AAAAA (1234)
BBBB BBBBBBBBB (2345)

I want to keep all alphabets in one column, and separate (****) int
one colum, so that the user is able to sort data either by name o
number.....
Plzzz hel
 
Select the column, do data>text to columns, select delimited,
click next, check other and put ( as delimiter, click finish
Select the new column that looks like 1234)
do edit>replace, find what ) leave replace with blank
click OK
 
I presume you want to keep the original intact, so you probably
want two helper columns (extra columns) for more flexibility.
The following returns the original value if no left paren,
and doesn't worry about missing right paren.

B1: =IF(ISERR(FIND("(",A1)),TRIM(A1),TRIM(LEFT(A1,FIND("(",A1)-1)))
C1: =IF(ISERR(FIND("(",A1)),"",TRIM(SUBSTITUTE(RIGHT(A1,LEN(A1)-FIND("(",A1)),")","")))
--or-- if you want number as number and only have numbers in parens not "(123x)"
C1: =IF(ISERR(FIND("(",A1)),"",TRIM(SUBSTITUTE(RIGHT(A1,LEN(A1)-FIND("(",A1)),")",""))+0)

additional possibilities in on my sorting.htm page.

Worksheet Functions will work faster, but for more complicated things
you might have some interest in ...
Extraction of a Group of Digits and Dashes, posted by Harlan Grove
http://www.mvps.org/dmcritchie/excel/grove_digitsid.htm

"Plzzz" takes a little bit interpretation and is not professional, I would
use "Please" to make things easier to read. Besides many people in
these groups do not use English as their primary language.


Peo Sjoblom said:
Select the column, do data>text to columns, select delimited [..trimmed]
Click to expand...
 
Back
Top