cut a bit out and concatenate

NCay

Board Regular
Joined
Jul 15, 2002
Messages
214
I have a column (B)with names and need to create a new column (C) that includes this name along with a prefix. Not too difficult...
= "text prefix" & cell ref
But...in some instances there is superfluous info in the original name, that I do not need to include. Any ideas how to this? The extra info is at the start of original data in some Coloumn B cells.
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Derek

Well-known Member
Joined
Feb 16, 2002
Messages
1,592
Hi there
I think you will need to give some examples of the entries in B and what is superfluous.
regards
Derek
 

NCay

Board Regular
Joined
Jul 15, 2002
Messages
214
Column B would contain...
"name NCay"
"name Jbloggs"
"name MMouse"

I need to keep the "NCay", "Jbloggs" "MMouse" and scrap the "name".

Does this help?
This message was edited by NCay on 2002-09-09 17:58
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
So - either exract the first word in the string, or, if the first word is "name", extract the second word in the string?
 

NCay

Board Regular
Joined
Jul 15, 2002
Messages
214

ADVERTISEMENT

How?
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
That wasn't an instruction! It was a question to see if I'd understood your problem right :)

assuming I have...

1) download the morefunc add in from here:

http://longre.free.fr/english/index.html

2) Install it & select it in the tools | Add-ins... menu.

3) Use a formula of this sort to extract the info:

=IF(LEFT(A1,4)="name",WMID(A1,2,1),WMID(A1,1,1))

or indeed:

=IF(WMID,A1,1,1)="name",WMID(A1,2,1),WMID(A1,1,1))


wmid() is a text function that works at the level of words rather than characters.

See the example:
Book6
ABCD
1NcayNcay
2nameNcayNcay
3nameNcaystuffNcay
4
5
Sheet3



You could do the equivalent using native excel text functions, but it would
a) be longer to write, and
b) given you an excuse not to download the morefunc add-in, which everyone should do on account of the usefulness of the functions it contains!

That said, post back if you have an objection to using the wmid route..

Paddy
This message was edited by PaddyD on 2002-09-09 18:21
 

Forum statistics

Threads
1,144,734
Messages
5,725,982
Members
422,652
Latest member
Elnene1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top