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

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Hi there
I think you will need to give some examples of the entries in B and what is superfluous.
regards
Derek
 
Upvote 0
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
 
Upvote 0
So - either exract the first word in the string, or, if the first word is "name", extract the second word in the string?
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,938
Members
448,534
Latest member
benefuexx

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
Back
Top